Here's an alternative to my alternative and doesn't matter if the city name
is more than a single word:

A1 = 3700 Chestnut New York

Formula in B1:

=TRIM(SUBSTITUTE(A1,"New York",""))

Returns: 3700 Chestnut

Formula in C1:

=MID(A1,SEARCH("new york",A1),255)

Returns: New York

The formula in c1 is kind of redundant if all the city names are the same!
You could just manually type one city name and then copy.

Biff

"Biff" wrote in message
...
NB:

You said: "Each cell has a different length of address, but the same city
name."

Based on your example, the city name is one word. If the city names are
more than one word:

New York
Kansas City
St. Louis

Then those formulas won't work and maybe Text to Columns is your best
solution.

Biff

"Biff" wrote in message
...
Hi!

You could use T to C but then afterwards you'd have to concatenate the
components of the address less the city.

Here's an alternative:

A1 = 3700 Chestnut Pasadena

Formula in B1:

=LEFT(A1,FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

Returns: 3700 Chestnut

Formula in C1:

=MID(A1,FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

Returns: Pasadena

After you get all the addresses parsed select all the formulas in columns
B and C and do a Copy/Paste Special/Values then if you want, you can
delete the original list.

Biff

"gbeard" wrote in message
...
Can I reverse the cell so the city is first and then separate the cell
based on the length of the city name?

--
Gary Beard