1

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
4
  • 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 Feb 4, 2012 at 1:08
  • ideally it could support 100s.1000s of value
    – toop
    Feb 4, 2012 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 Feb 4, 2012 at 1:20
  • @Raystafarian - would you suggest rolling it in VBA instead?
    – toop
    Feb 4, 2012 at 3:29

1 Answer 1

4

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

You must log in to answer this question.

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