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

pjstu3

New member
Joined
Jan 22, 2014
Messages
3
Reaction score
0
Points
0
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

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
how are you at VBA?
You could do this fairly easily with code.
Try looking up "instr"
 

pjstu3

New member
Joined
Jan 22, 2014
Messages
3
Reaction score
0
Points
0
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

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
could you provide a sample workbook with what you are trying to accomplish? that may help get more responses to help.
 

pjstu3

New member
Joined
Jan 22, 2014
Messages
3
Reaction score
0
Points
0
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!
 
Top