Excel 2007 : Finding substrings in a string

**kkerr** · 12-28-2010, 03:02 PM

Hello,

I want a formula that will look at a string and find one of several possible substrings, then output the matching substring.

Example:

String (Column A)

yellow

Valid Substrings (Column B)

llo
ang
lue
re

The formula should return "llo" for yellow.

**DonkeyOte** · 12-28-2010, 03:10 PM

If we assume yellow in A1 and sub strings in B1:B4 then:

Please Login or Register  to view this content.

should multiple sub strings be found the above construct will return the last sub string listed found in main string

**kkerr** · 12-28-2010, 03:33 PM

Awesome! Worked like a dream! Many, many thanks!

**Dandy** · 06-19-2012, 09:51 AM

I know this is an old post, but would someone care to explain what happens here? Although it actually works for me in an example worksheet, I need to modify it to adapt it to my needs, and I can't do that without understanding it.

First, in the documentation I read, SEARCH takes three arguments (text1, text2, start_position), and it looks like text1 is one cell, not a range of cells. When I try the above without embedding it in lookup, all I get is #VALUE...

Second, lookup is supposed to have two possible types of arguments: either LOOKUP(lookup_value,lookup_vector,result_vector) or LOOKUP(lookup_value,array). Obviously here it must be the 1st, since there are three arguments, but what is this "9.99E+307"? The 1st argument is supposed to be the thing you are looking for. How does "9.99E+307" relate to "yellow" or a substring of it?

Sorry if I'm being dense...