Find Variation (I want to find the first character that is NOT a particular value)

[gordonpsmith]

Quirky one, this...

I have a string of characters that can be a variety of values, such as:

xxxxxXXXxxXXxxxxxxXXGGXXXXXXXXWWWXXXXXXxxxxxxxxxxxxxxxxxxxxxXXWWWWXXXGGGGXxxXXxXXxxxxxxxxxxxxxxx

I want counts of characters in order, such as:
x5X3x2X2x6X2G2.....

If I could wire FIND to tell me the first character that is not, say "x", I could set up a series of substrings that pulls that out.
LEFT(String,1) gives me the first character
FIND(<>LEFT(String,1)), if it worked, would tell me how many characters are in line
RIGHT(String,LEN(String)-FIND(<>LEFT(String,1))) gives me what's left and I rinse and repeat.

It's getting Excel to find me the location of when a character changes from the first value in a string that's stymieing me

Thanks!

 

[StephenCrump]

A couple of questions please:

- xxxxxXXX ---> x5X3 suggests the character comparison should be case sensitive?

- What version of Excel are you running? Ideally, you'd tell us this in your Account details.

- Are you open to a VBA solution, or should it be a formula?

 

[gordonpsmith]

Office 365.

Yes, I'm hoping for case sensitive, but if it proves troublesome I can always do an intermediate substitute.

I've got a brute force workaround in place that limps along for what I need. It could be done in VBA, but the end user doesn't know how to use that, so I'm prioritizing formulas at this point. I guess this was more of a "shouldn't I be able to do it this way" kind of question as opposed to "how can I do this"

For completeness, this particular run has only 5 unique characters (but others down the road could have more). The workaround:

• pulls the first character of the string via LEFT (the first part of the output coding)
• I then have five columns (one for each option). For each column:
  • If the column character reference is the first character of the string, it spits out a value of 1000
  • If it cannot find the column character reference in the string (which would result in an error, it spits out a value of 1000
  • Otherwise, it runs a FIND to return the first appearance of that column character reference
• A sixth column takes the minimum value associated with those 5, which then leads me to how many characters are in a row before hitting a different character (the second part of the output coding)
• It closes out by taking RIGHT of the character string, reducing the overall length by the number of column character references it just found

...and the rest is rinse and repeat. So it works, but inelegantly...

 

[Eric W]

Try:

Book1
	A
1	xxxxxXXXxxXXxxxxxxXXGGXXXXXXXXWWWXXXXXXxxxxxxxxxxxxxxxxxxxxxXXWWWWXXXGGGGXxxXXxXXxxxxxxxxxxxxxxx
2
3
4	x5X3x2X2x6X2G2X8W3X6x21X2W4X3G4X1x2X2x1X2x15
Sheet8

Cell Formulas
Range	Formula
A4	A4	=LET(str,A1,s,SEQUENCE(LEN(str)),e,EXACT(MID(str,s,1),MID(str&CHAR(1),s+1,1)),wa,CONCAT(IF(e,"",MID(str,s,1))),wb,IF(e,0,s),wc,FILTER(wb,wb>0),m,SEQUENCE(LEN(wa)),CONCAT(MID(wa,m,1)&wc-IF(m=1,0,INDEX(wc,m-1))))

I suspect it could be done easier with SCAN or a recursive LAMBDA, but my version of Excel doesn't have those.

 

[StephenCrump]

Sure, why not. Recursive LAMBDA approach:

=MySplit(A1)

MySplit: =LAMBDA(s,IF(LEN(s),LET(f,FIND(LEFT(SUBSTITUTE(s,LEFT(s,1),""),1),s),LEFT(s,1)&IF(f=1,LEN(s), f-1&MySplit(RIGHT(s,1+LEN(s)-f)))),"-"))

 

[Peter_SSs]

Assuming that you have the new REGEX functions, what about this direct formula?

25 02 21.xlsm
	A	B
1	xxxxxXXXxxXXxxxxxxXXGGXXXXXXXXWWWXXXXXXxxxxxxxxxxxxxxxxxxxxxXXWWWWXXXGGGGXxxXXxXXxxxxxxxxxxxxxxx	x5X3x2X2x6X2G2X8W3X6x21X2W4X3G4X1x2X2x1X2x15
2	rrRRXxTT	r2R2X1x1T2
3	Test	T1e1s1t1
Count characters

Cell Formulas
Range	Formula
B1:B3	B1	=LET(t,TEXTSPLIT(REGEXREPLACE(A1,"(.)(?!\1|$)","$1|"),"|"),CONCAT(LEFT(t,1)&LEN(t)))

 

[Peter_SSs]

.. or simpler

25 02 21.xlsm
	A	B
1	xxxxxXXXxxXXxxxxxxXXGGXXXXXXXXWWWXXXXXXxxxxxxxxxxxxxxxxxxxxxXXWWWWXXXGGGGXxxXXxXXxxxxxxxxxxxxxxx	x5X3x2X2x6X2G2X8W3X6x21X2W4X3G4X1x2X2x1X2x15
2	rrRRXxTT	r2R2X1x1T2
3	Test	T1e1s1t1
Count characters (2)

Cell Formulas
Range	Formula
B1:B3	B1	=LET(t,REGEXEXTRACT(A1,"(.)\1*",1),CONCAT(LEFT(t,1)&LEN(t)))