Excel: Retrieve rows based on string search

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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