Find the ROW number?

[Guest]

I have a worksheet with multiple sheets.
I want to do a text search in column A of sheet 1. I want the ROW number
where it finds this text first to be displayed. (The text is sometimes only
part of the whole text that is entered in a cell). For example some cells
might have "hello this is a test" where as others might just have the word
"test". I want to find the first ROW (the lowest number) where the word
"test" is found.

Please advise

 

[Biff]

Hi!

Assume you enter your search criteria in cell C1. Enter
one of these formulas in D1. Both are array formulas and
must be entered as CTRL,SHIFT,ENTER:

This formula will return the actual cell address:

=ADDRESS(SMALL(IF(ISNUMBER(SEARCH
(" "&C1&" "," "&A1:A6&" ")),ROW(A1:A6)),1),1)

This formula will return the row number, that is, the
relative position of the search criteria within the range
searched:

=MATCH(TRUE,ISNUMBER(SEARCH(" "&C1&" "," "&A1:A6&" ")),0)

Biff

 

[Frank Kabel]

Hi Biff
shorter version:
=MATCH("*" & C1 & "*",A1:A6,0)

or
=CELL("address",INDEX(A1:A6,MATCH("*" & C1 & "*",A1:A6,0)))

no need for array formulas in this case

 

[Biff]

Hi Frank!

Consider this array:

greatest
tests
tested
test this
not a test

Those formulas will fail if the search criteria is the
explicit word (string) "test".

I've found that inserting the leading and trailing spaces
in both the search criteria and the search range is *very*
reliable.

Biff

 

[Frank Kabel]

Hi Biff
now it depends what the OP wants. My formulas would return
A1 as match as this cell contains the word 'test'. But
you'Re of course right if the OP only wants whole words
(so in this example 'greatest' should not be considered a
match)

Frank