Search substring within range, report the substring found

**Brooke1578** · 12-16-2012, 09:22 AM

Hi Everyone,

What i have is a single worksheet that has a range from A2 to G30,000 that contains various text and numbers - there is nothing similar, these are random. At the end of this range, in column H, i have a list of strings that i want to search within the A2:G30,000 range. I can find formulas through online searches that can search for any of these sub-strings within a specified range and report either an "X" or some other text when a matching sub-string is found, but what i want to report is the sub-string that was actually found.

Can this be done??

I'm new to the forum and noticed on my post it has listed excel 2003... I'm actually using Excel 2010.. I'll suss out how to fix this, but i wanted to just add this comment to my post if anyone tries to help me in the mean time.

Cheers!

**Jakobshavn** · 12-16-2012, 09:44 AM

Try:

Please Login or Register  to view this content.

**Brooke1578** · 12-18-2012, 11:28 PM

Hi Thanks so much for your prompt reply. I actually though wanted the string that was found to be listed in column I, not an "X" .

**:) Sixthsense :)** · 12-18-2012, 11:40 PM

Please attach a sample workbook for better understanding.

**Brooke1578** · 12-20-2012, 03:56 PM

Hi Sixthsense.. I think i've actually stumbed across something that gives me my answer:

=IFERROR(LOOKUP(2^15,SEARCH(Search_Strings,G2),Search_Strings),"")

Where Search_Strings is the named range and contains the strings to be searched
G2 is the cell to be searched

Thanks for your help!