Truncate text strings using LEFT and FIND/SEARCH for multiple search terms

[ngdoherty]

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

 

[Aladin Akyurek]

Does this...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"_reg_","[","shiftflop"},C2))),C2,"")

help? If not, care to restate the problem in words (not in formulas) by means of an example?

 

[ngdoherty]

Thanks Aladin,

But that did not work - nothing was truncated.

Here is an example of the data

pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/irp[0]
pathA/path_X/path_Y/path_Z/irp[1]
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path__AA/path__AB/shift_flop/sigA
pathA/path_X/path_Y/path_Z/irp[9]
pathA/path_X/path__AA/path__AB/shift+flop/sigB


There is other data (on same row) associated with each entry.
So what I am trying to do is to create a new column - that will remove the "unique" training text for each row.

so that
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d

simply becomes

pathA/path_X/path_Y/path_Z/lso0_rxs
pathA/path_X/path_Y/path_Z/lso0_rxs


With this new column I can then use Pivot Table to analyze the other data on the row - for each entry that macthes

pathA/path_X/path_Y/path_Z/lso0_rxs

Same applies for he other strings that I truncate.

The LEFT formulas that I used above work well for singel cases - but not where I try to add 3 or more string searches into the formula.

Any ideas why???

Thanks

 

[Aladin Akyurek]

Thanks Aladin,

But that did not work - nothing was truncated.

Here is an example of the data

pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/irp[0]
pathA/path_X/path_Y/path_Z/irp[1]
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path__AA/path__AB/shift_flop/sigA
pathA/path_X/path_Y/path_Z/irp[9]
pathA/path_X/path__AA/path__AB/shift+flop/sigB


There is other data (on same row) associated with each entry.
So what I am trying to do is to create a new column - that will remove the "unique" training text for each row.

so that
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d

simply becomes

pathA/path_X/path_Y/path_Z/lso0_rxs
pathA/path_X/path_Y/path_Z/lso0_rxs


With this new column I can then use Pivot Table to analyze the other data on the row - for each entry that macthes

pathA/path_X/path_Y/path_Z/lso0_rxs

Same applies for he other strings that I truncate.

The LEFT formulas that I used above work well for singel cases - but not where I try to add 3 or more string searches into the formula.

Any ideas why???

Thanks

pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d	pathA/path_X/path_Y/path_Z/lso0_rxs
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d	pathA/path_X/path_Y/path_Z/lso0_rxs
pathA/path_123/path_456/data_out_reg_17_0/d	pathA/path_123/path_456/data_out
pathA/path_123/path_456/data_out_reg_0_0/d	pathA/path_123/path_456/data_out
pathA/path_X/path_Y/path_Z/irp[0]	pathA/path_X/path_Y/path_Z/irp
pathA/path_X/path_Y/path_Z/irp[1]	pathA/path_X/path_Y/path_Z/irp
pathA/path_123/path_456/data_out_reg_4_0/d	pathA/path_123/path_456/data_out
pathA/path_X/path__AA/path__AB/shift_flop/sigA	pathA/path_X/path__AA/path__AB/
pathA/path_X/path_Y/path_Z/irp[9]	pathA/path_X/path_Y/path_Z/irp
pathA/path_X/path__AA/path__AB/shift+flop/sigB	pathA/path_X/path__AA/path__AB/

<tbody>
</tbody>

B2, just enter and copy down:

=IFERROR(LEFT(A2,LOOKUP(9.99999999999999E+307,SEARCH({"_reg_","[","shift*flop"},A2)-1)),A2)

 

[ngdoherty]

Thanks Aladin,

That worked great.

Could you do me a favour and explain what the code is actually doing?
Don't quite understand the LOOKUP(9.999 element, and not sure why the SEARCH didn't work for me?

Thanks again.


Neil

 

[Aladin Akyurek]

Thanks Aladin,

That worked great.

You are welcome.

Could you do me a favour and explain what the code is actually doing?
Don't quite understand the LOOKUP(9.999 element, and not sure why the SEARCH didn't work for me?

Thanks again.


Neil

LOOKUP(BigNum,Reference) delivers the last numeric value from reference.

Reference in our case consists of:

SEARCH({"_reg_","[","shift*flop"},A2)-1)

This is an array that tells the positions at which the items "_reg_","[","shift*flop" are found in A2 like:

{35,#VALUE!,#VALUE!}

LOOKUP passes 35 to LEFT returning the desired outcome.

Here some links on LOOKUP and kindered functions in formulas for the last numeric value...

http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html (post #7)