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