4

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.

1
  • 1
    Well done for showing a very clear example with the results you would expect, which made the problem much easier to address in detail. – AdamV Feb 22 at 13:04
5

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),"")))

enter image description here

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)*

1
  • 1
    Yes by definition there is only one transition so this is a very nice way to do it. Thank you very much. – Inselino Feb 22 at 14:17
4

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.

1
  • 1
    Also a nice solution. By definition, there will always be one and only one transition. – Inselino Feb 22 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.