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!

 

[Peter_SSs]

Some more options with/without LET

21 02 19.xlsm
	A	B	C
1
2	123CA,456US22,789VT	456US22	456US22
3	123CA8,6US123,789VT	6US123	6US123
4	123CA,56US9,789VT999	56US9	56US9
5	123CA,789VT7KUHKH,351TS3LJ3,456US22	456US22	456US22
6	123CA,456US2233445,789VT7KUHKH,351US3LJ3	456US2233445	456US2233445
7	123CA,789VT7KUHKH,351US3LJ3,456US22	351US3LJ3	351US3LJ3
8	999456US22,123CA,789VT7KUHKH,351US3LJ3	999456US22	999456US22
9	123CA,456US22,789VT7KUHKH,351US3LJ3	456US22	456US22
SEARCH

Cell Formulas
Range	Formula
B2:B9	B2	=LET(ext,SUBSTITUTE(","&A2,",",REPT(" ",50)),TRIM(MID(ext,SEARCH("US",ext)-50,100)))
C2:C9	C2	=TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",50)),SEARCH("US",SUBSTITUTE(","&A2,",",REPT(" ",50)))-50,100))