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

ngdoherty

New Member
Joined
Apr 16, 2013
Messages
5
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Apr 16, 2013
Messages
5
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Apr 16, 2013
Messages
5
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top