Searching for a string in a multi-lined row

Hi everyone,

I need suggestions on the best approach for searching for a piece of string from a multi-lined cell. I am trying to do it via VBA but can't seem to figure it out. I tried FIND and Instr.
I was thinking of converting the muliple lines into a single line then do an instr comparison from the result but I don't know how to do it.

Sample:

String to search = "MARY"
Output Location value

Table:
Location-----Names
US ----------JOHN AND RINGO
----------MARTHA YULET

UK-----------JAMES THE FIRST
-----------HENRY THE FIFTH
-----------MARY HAD A LITTLE LAMB
-----------JOHNNY BRAVO

Cells in column "Names" has multiple lines
Cells in column "Location" are not merged

any help is appreciated. thanks.

Hi,

Search into single or multi lines is the same for me ! Easy with Find method !

So post a sample workbook within your code and an example of the wanted result …

To expand, for your situation, Instr would report the character location of the search word. See the example (assuming columns A & B)

Please Login or Register  to view this content.

Thanks both. I went back to my code and tweaked it.
I stand corrected, FIND function works like a charm.

Please Login or Register  to view this content.