Using SEARCH to extract from concatenated field

happyhungarian

Board Regular
Joined
Jul 19, 2011
Messages
226
Office Version
  1. 365
Platform
  1. Windows
Hi, I have a data that is in a long concatenated field. The issue I have is I only know a portion of the section I am looking for but I need to pull back the entire corresponding section. For example, if I have a concatenated field of "123CA,456US,789VT" on which I may do a SEARCH for "US" but I need my formula to return "456US". So I was thinking of using the "SEARCH" function to find the spot where "US" appears {in this case it would be "10"}, find the location of the commas that comes just before and just after that position so that the formula returns "456US". Any thoughts on how to do this?

Thanks!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,832
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Here's one way:

Book3.xlsx
ABCD
1Search forResult
2123CA,456US,789VTUS456US
3123CA,456US,789VTCA123CA
4123CA,456US,789VTVT789VT
Sheet776
Cell Formulas
RangeFormula
D2:D4D2=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,SEARCH(C2,A2)+LEN(C2)-1),",",REPT(" ",255)),255))
 

happyhungarian

Board Regular
Joined
Jul 19, 2011
Messages
226
Office Version
  1. 365
Platform
  1. Windows
Thanks! Now what if there are also characters after the "US"? For example "456US22". Looks like this would trim the "22" off.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,832
Office Version
  1. 2016
Platform
  1. Windows
OK, since you Only gave 1 sample...
Are your text strings Always 3 sections separated by 2 commas?
Are the separate sections always the same length?
Do you Only want the section containing "US"?
Please give a few more samples that are representative of your data set.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,361
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

As well as answering jtakw's questions, can you also please update your version details as requested previously?
I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

happyhungarian

Board Regular
Joined
Jul 19, 2011
Messages
226
Office Version
  1. 365
Platform
  1. Windows
Hi, no the text strings between the commas can be all different lengths. I would only want the section containing the first occurrence of "US". For example, the data set could be "123CA,456US22,789VT7KUHKH,351US3LJ3". If I searched for "US" I would want it to pull back "456US22". That's why I was thinking the SEARCH function would be good because it would find the first occurrence of "US"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is there any reason why you have not update your account details as requested?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,832
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your reply, a bit messy, but it works.
There may be simpler options with 365, which I don't have.

Book3.xlsx
AB
2123CA,456US22,789VT456US22
3123CA8,6US123,789VT6US123
4123CA,56US9,789VT99956US9
5123CA,789VT7KUHKH,351TS3LJ3,456US22456US22
6123CA,456US2233445,789VT7KUHKH,351US3LJ3456US2233445
7123CA,789VT7KUHKH,351US3LJ3,456US22351US3LJ3
8999456US22,123CA,789VT7KUHKH,351US3LJ3999456US22
Sheet776
Cell Formulas
RangeFormula
B2:B8B2=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2)),LEN(LEFT(A2,SEARCH("US",A2)))-LEN(SUBSTITUTE(LEFT(A2,SEARCH("US",A2)),",",""))+1),LEN(A2)),",",REPT(" ",LEN(A2))),LEN(A2)))


Note Row 7 result, you said the First occurrence of "US", which is not "456US22"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Apologies. Now updated.
Many thanks.
Two option, depending on if you have the LET function
+Fluff 1.xlsm
ABC
20With LETWithout
21123CA,456US22,789VT7KUHKH,351US3LJ3456US22456US22
Main
Cell Formulas
RangeFormula
B21B21=LET(Ary,FILTERXML("<L><M>"&SUBSTITUTE($A21,",","</M><M>")&"</M></L>","//M"),INDEX(Ary,MATCH("*US*",Ary,0)))
C21C21=INDEX(FILTERXML("<L><M>"&SUBSTITUTE($A21,",","</M><M>")&"</M></L>","//M"),MATCH("*US*",FILTERXML("<L><M>"&SUBSTITUTE($A21,",","</M><M>")&"</M></L>","//M"),0))
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,827
Members
415,859
Latest member
Vain

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