Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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.

Input

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
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:-

=VLOOKUP("*_"&A2&".*",B:B,1,FALSE)

and then drag down this till end.

For your quick reference, below is my screenshot

enter image description here

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .