Find a string withing cell range and return cell number

[visak]

Hi,

I have a column "A" with many alphanumeric values.
How to find a specific string (only one in the range) and to return the cell number where the string is located?

see the example example (the string is "red"):

[werfnort]

Here you go. Simply replace "red" with a different string, or reference to another cell, and update the ranges to where you want it to search!

=MAX(IF(ISNUMBER(SEARCH("red",$A$1:$A$5,1)),ROW($A$1:$A$5),0))

You'll need to array enter once you type in the formula - usually CTRL+SHIFT+ENTER.

Note: If there's more than one "red" - it will return the bottom-most row. If it's not in that row, it will return a 0.

Note 2: If you want it to be case-sensitive, change "SEARCH" to "FIND"

[DonkeyOte]

Perhaps

=MATCH("*red*",A1:A5,0)

Note however that in the above (and indeed the prior array) should the keyword be embedded (eg "hundred") then it follows that this particular approach is open to error.

Based on your sample you can avoid this by replacing commas and period delimiters with spaces such that you have a common delimiter (and therefore normalised strings), at which point:

=LOOKUP(9.99E+307,SEARCH(" red "," "&SUBSTITUTE(SUBSTITUTE(A1:A5,","," "),"."," ")&" "),ROW(A1:A5))

should do what you want.

Note: that both this and the above shall return #N/A if not found (rather than 0 per the Array) - this can of course be catered for as per your preference.

[visak]

Thanks! It works fine.

[visak]

It works...thank you!

[paulwenman]

I am trying to apply this to a similar situation without success. For example I want to know if the contents of each cell in column A. (e.g.A. Noble & Son) exist as a substring anywhere in column E. Match does not have to be case sensitive. Justa simple yes/NO, 1/0 answer will do. Any ideas? Thanks.

Excel snap.jpg

[protonLeah]

paulwenman,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.