Excel: Retrieve rows based on string search

Closed
Dennis - Aug 18, 2010 at 07:39 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 18, 2010 at 07:44 AM
Hello,

I'm trying to write a fairly simple piece code (or so I thought) to do the following:
In column A on sheet1 in my Excel workbook, I have strings of 5 characters each.
In column A on sheet2 in the same workbook, there are strings of 40 characters each.
I'm trying to retrieve the 40 character strings and put them in column B on sheet1 if this 40 character string contains the 5 character string from sheet1.
It's possible to find more than one 40 character string containing the 5 character string and I would like to show them all in column B on sheet1 per 5 character string.

I hope someone can help me out or at least give me some useful tips.

Thanks in advance.

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 18, 2010 at 07:44 AM
I would say you need to use two functions

a. Match. This would allow you to use wild card so you can locate the sub string and will give you the row number where the match occured.

b. indirect. since match gives you the row number, and assuming columns and sheet name are static, you can hard code them and get to the result indirectly
0