MS Excel: How to get a string of repeating letters from a bigger string?

Question

After my measurements I end up with a row of failed and passed tests (x = failed, o = passed). This row starts with x or o and changes one time.

Example A) xxxoo
Example B) ooox
Example C) ooxxxxx

I am now searching for a function that gives me the second part of the string where all letters are the same up to a maximum of 4 letters. So the results will be:

A) oo
B) x
C) xxxx

I know that I can get the last digits with RIGHT() but I don't know how to check for the length of the "only x" or "only o" part at the end.

Answer 1

Given the constraints on your data, where there is only a single transition, you can just repeat the rightmost character the number of times it exists in your string:

=REPT(RIGHT(A1),LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(A1),"")))

Not sure if the "four letters" refers to a data constraint or a formula function, but if there might be more than four letters, and you only want to return the last four, then embed the above formula in a RIGHT function: =right(the_formula,4)*

Answer 2

If the string can only change once, then all you need to do is "throw away" the left hand characters, then limit the remainder to 4. "Substitute" will swap a string for another, so you can swap the leftmost character for nothing, however many times it appears.

=LEFT(SUBSTITUTE(A1,LEFT(A1,1),""),4)

If the string could change zero times eg ooooo or xxxxxx then this will give an empty result. This might be considered correct if you only ever want the second letter since there isn't one.