Select specific text from one cell and move to another cell

I have a file with address information without any delimiting characters. I would like to search multiple cells looking for specific information e.g. "Avon" and move this information to another cell in the worksheet. How can I do this?

e.g. this is what the data looks like now:
18401 377th St Avon
12345 Shorewood Dr Avon

The result I want is the following... the street address in one cell and the city in another.


"18401 377th St" in one cell
"12345 Shorewood Dr" in one cell

"Avon" in another cell
"Avon" in another cell

try this, suppose your text string in column A:
in Column B: =LEFT(A1,SEARCH("Avon",A1)-1)
in Column C: =SUBSTITUTE(A1,B1,"")

Originally Posted by acerrhod

try this, suppose your text string in column A:
in Column B: =LEFT(A1,SEARCH("Avon",A1)-1)
in Column C: =SUBSTITUTE(A1,B1,"")

Thanks for the help ... this seems to be working for me. Cliff

Thanks... I think I got my answer. Cliff