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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Some more options with/without LET

21 02 19.xlsm
ABC
1
2123CA,456US22,789VT456US22456US22
3123CA8,6US123,789VT6US1236US123
4123CA,56US9,789VT99956US956US9
5123CA,789VT7KUHKH,351TS3LJ3,456US22456US22456US22
6123CA,456US2233445,789VT7KUHKH,351US3LJ3456US2233445456US2233445
7123CA,789VT7KUHKH,351US3LJ3,456US22351US3LJ3351US3LJ3
8999456US22,123CA,789VT7KUHKH,351US3LJ3999456US22999456US22
9123CA,456US22,789VT7KUHKH,351US3LJ3456US22456US22
SEARCH
Cell Formulas
RangeFormula
B2:B9B2=LET(ext,SUBSTITUTE(","&A2,",",REPT(" ",50)),TRIM(MID(ext,SEARCH("US",ext)-50,100)))
C2:C9C2=TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",50)),SEARCH("US",SUBSTITUTE(","&A2,",",REPT(" ",50)))-50,100))
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

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