Using SEARCH to extract from concatenated field

[happyhungarian]

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!

 

[jtakw]

Hi,

Here's one way:

Book3.xlsx
	A	B	C	D
1			Search for	Result
2	123CA,456US,789VT		US	456US
3	123CA,456US,789VT		CA	123CA
4	123CA,456US,789VT		VT	789VT
Sheet776

Cell Formulas
Range	Formula
D2:D4	D2	=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,SEARCH(C2,A2)+LEN(C2)-1),",",REPT(" ",255)),255))

 

[happyhungarian]

Thanks! Now what if there are also characters after the "US"? For example "456US22". Looks like this would trim the "22" off.

 

[jtakw]

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]

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]

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]

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

 

[happyhungarian]

Apologies. Now updated.

 

[jtakw]

Thanks for your reply, a bit messy, but it works.
There may be simpler options with 365, which I don't have.

Book3.xlsx
	A	B
2	123CA,456US22,789VT	456US22
3	123CA8,6US123,789VT	6US123
4	123CA,56US9,789VT999	56US9
5	123CA,789VT7KUHKH,351TS3LJ3,456US22	456US22
6	123CA,456US2233445,789VT7KUHKH,351US3LJ3	456US2233445
7	123CA,789VT7KUHKH,351US3LJ3,456US22	351US3LJ3
8	999456US22,123CA,789VT7KUHKH,351US3LJ3	999456US22
Sheet776

Cell Formulas
Range	Formula
B2:B8	B2	=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]

Apologies. Now updated.

Many thanks.
Two option, depending on if you have the LET function

+Fluff 1.xlsm
	A	B	C
20		With LET	Without
21	123CA,456US22,789VT7KUHKH,351US3LJ3	456US22	456US22
Main

Cell Formulas
Range	Formula
B21	B21	=LET(Ary,FILTERXML("<L><M>"&SUBSTITUTE($A21,",","</M><M>")&"</M></L>","//M"),INDEX(Ary,MATCH("*US*",Ary,0)))
C21	C21	=INDEX(FILTERXML("<L><M>"&SUBSTITUTE($A21,",","</M><M>")&"</M></L>","//M"),MATCH("*US*",FILTERXML("<L><M>"&SUBSTITUTE($A21,",","</M><M>")&"</M></L>","//M"),0))