Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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_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 
year             201284_blah_year.ext 
type             701284_blah_type.dat
share|improve this question
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
up vote 4 down vote accepted

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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