Search a column for a text string

**shawnh** · 04-05-2011, 08:27 PM

Hi, I need to find the cell that has a text string (a number actually), that is at the beginning of the rest of some other text. To illustrate, in column C I have cells that contain this:

965 Los Angeles Dodgers C Kershaw - L
966 Colorado Rockies J Chacin - R
.
.
.

I need to do a search on all the cells in column C looking for the cell that contains the number, say, 966. I can't really use MATCH or LOOKUP because they would be looking for a cell that has 966 in it by itself - in other words they look for a perfect match. I need something that will detect my search string (966) *anywhere* within the cells to be searched. And if this function could return the exact cell location where it's found, that would be perfect!

Thanks!
Shawn

**teylyn** · 04-05-2011, 08:35 PM

How about

="C"&MATCH("*966*",C1:C200,0)

cheers,

**shawnh** · 04-05-2011, 11:18 PM

Thanks Teylyn, that works great. I forgot to mention that I need to take what your MATCH statement returns (let's say C15) and plop that into a "Range" statement in order to *then* get the contents of cell C15. So to illustrate, lets say our MATCH statement resides in cell F4. It does it's thing looking for 966 and finds it in cell C15, so now the value of cell F4 is the text string "C15", right? Okay, now I want to have a "Range" statement that will look at cell F4... see that it's value is "C15" and realize that it is not a "literal" value, but that it must then go one more step and get the value of cell C15. Can the "Range" statement do that?

Thank You!
Shawn

**teylyn** · 04-05-2011, 11:21 PM

You can use the Indirect() function for that.

Either

=Indirect(F4)

or without the helper cell in F4, use

=indirect("C"&MATCH("*966*",C1:C200,0))

cheers,

**teylyn** · 04-05-2011, 11:23 PM

Taking this a step further: You probably do not want to hard-code the number into the formula. So instead of using the 966 in the formula, write 966 into a cell, let's say A1. Then you can use

=INDIRECT("C"&MATCH("*"&A1&"*",C1:C200,0))

Change the number in A1 and, hey, presto ...