How to track the number of changes from 'x' to 'o' in a cell that only contains-CodePudding

I am using a tally of 'x' and 'o' to track occurrences in a single cell.

In the sample cell A1 with value "xxxoxxo" I would like a formula (or two separate ones) that tells how many times the values changes from 'x' to 'o' and separately from 'o' to 'x'.

Above, it would output 2 changes from 'x' to 'o' and 1 change from 'o' to 'x'.

I've been trying to use LEN and SUBSTITUTE but can only get the amount of times each letter occurs, not the number of changes from one to the other. This is what I currently have:

For X: =LEN(A1)- LEN(SUBSTITUTE(A1,"x",""))

For O: =LEN(A1)- LEN(SUBSTITUTE(A1,"o",""))

CodePudding user response：

you can use these formulas:

for changes count from x to o in col B1:

=(LEN(A1)-LEN(SUBSTITUTE(A1;"xo";"")))/2

for changes count from o to x in col C1:

=(LEN(A1)-LEN(SUBSTITUTE(A1;"ox";"")))/2

According to your manner, this method finds the number of occurrences of xo to determine the change from x to o. The division by 2 is because of the length of string 'xo'.

CodePudding user response：

try:

=LEN(REGEXREPLACE(SUBSTITUTE(A1, "xo", "×"), "[xo]", ))

and:

=LEN(REGEXREPLACE(SUBSTITUTE(A1, "ox", "×"), "[ox]", ))