Hi,

I have a column of data - containing text strings.
The text strings are very similar (start and middle of strings are identical) but are unique due to characters at the end of each string.
I would like to truncate the text strings - by finding the last "common" sub-string - which I specify - and use LEFT to return the remainder.

The strings that I want to search for are
1) "_reg_"
2) "["
3) "shiftflop"


=IFERROR(LEFT(C2,FIND("_reg_",C2)-1),IFERROR(LEFT(C2,FIND("[",C2)-1),IFERROR(LEFT(C2,FIND("shiftflop",C2)-1),C2)))


This works for the first 2 cases - but NOT the last.
If I remove the first 2 and ONLY include the last it works fine!

=IFERROR(LEFT(C2,FIND("shiftflop",C2)-1),C2)))

Is there an error in the formula or a limitation when "nesting" like that ?
Is there any issue including the char "["

I also tried using search function:


=IFERROR(LEFT(A1,SEARCH({"[","_reg_","shiftflop"},A1)-1),A1)

I believe that SEARCH({"A","B","C"},A1)-1) is equivalent of
search for any of the letters A,B OR C in cell A1 and return the cell position for the fist match you find.
Is that correct?

Again this worked for some cases but not all.

I am not sure what the correct syntax is - so looking for some help.


Thanks