Search all cells in a column for text and if found provide the cell reference

[pjstu3]

Hi there,

I have a list of data around 2000 rows long in column A. I'd like to search through the list and when a record contains specific text I'd like the formula to return that row number (ideally the cell reference) so that I can perform additional manipulation of the text in the cell.
In the list of data there will be only around 4 records that have the string, but I need a formula/series of formulas that won't just stop at the first instance.

For testing purposes I've tried this {=SMALL(IF(A:A=F1,ROW(A:A)),ROW(1:1))} where F1 holds the exact value and it returns the row correctly. I then have 3 more rows with Row 2:2 etc on the end.

However I really need something like this {=SMALL(IF(A:A="*string*",ROW(A:A)),ROW(1:1))} where I can enter the string/text into the formula with a wild card on each end, but it just won't work!

Any ideas?


Regards,

Phil

 

[Simi]

how are you at VBA?
You could do this fairly easily with code.
Try looking up "instr"

 

[pjstu3]

Haven't used VBA in years. There are around 100 different errors I need to scan the data for, I feel VBA would be more work... once I can get this formula to work I can copy paste it everywhere. But thanks though

how are you at VBA?
You could do this fairly easily with code.
Try looking up "instr"

 

[Simi]

could you provide a sample workbook with what you are trying to accomplish? that may help get more responses to help.

 

[pjstu3]

Won the game.

could you provide a sample workbook with what you are trying to accomplish? that may help get more responses to help.

Just figured it out! Very proud of myself.

{=SMALL(IF(ISNUMBER(FIND(O39,Data!A:A)),ROW(Data!A:A)),ROW(Data!1:1))}
Where O39 holds the string I am searching for.
OR for a string in the formula
{=SMALL(IF(ISNUMBER(FIND("text goes here",Data!$A:$A)),ROW(Data!$A:$A)),ROW(Data!1:1))}

The last ROW argument is increased by one for the next instance you want to find, for example:
{=SMALL(IF(ISNUMBER(FIND(O39,Data!A:A)),ROW(Data!A:A)),ROW(Data!2:2))} etc

Hopefully this helps someone else in the future! Thanks for everyone's feedback though!