Is there a formula or script that will match sub-strings from one column in another column and return the full value of the sub-string match? e.g.


COL_a            COL_b
name             201284_blah_year.ext
address          407284_blah_name.xml
journal          501283_blah_journal_entries.ott
journal_entries  701284_blah_type.dat
year             401184_blah_journal.ext
type             301287_blah_address.txt

Desired output

COL_a            COL_b
name             407284_blah_name.xml 
address          301287_blah_address.txt
journal          401184_blah_journal.ext 
journal_entries  501283_blah_journal_entries.ott 
year             201284_blah_year.ext 
type             701284_blah_type.dat
  • Trying to figure out a macro or formula, but you can always just use text filters one by one depending on how many records there are – Raystafarian Feb 4 '12 at 1:08
  • ideally it could support 100s.1000s of value – toop Feb 4 '12 at 1:17
  • =IF(COUNTIF(B:B,"*"&A1&"*")>0, TRUE,FALSE) will give you a true or false depending on if the sub string is found in the array, just need to return the found value for true – Raystafarian Feb 4 '12 at 1:20
  • @Raystafarian - would you suggest rolling it in VBA instead? – toop Feb 4 '12 at 3:29

On Column C, write the below formula:-


and then drag down this till end.

For your quick reference, below is my screenshot

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.