text to columns

[gbeard]

I have a column whose cells contain an address and city in each cell. I'm
trying to separate the address from the city. Each cell has a different
length of address, but the same city name. Is it possible to, or what is
the easiest way, to separate the address and the city?

Here is a sample of my data:

A B
3700 Chestnut Pasadena
42 N. Hill Pasadena

I want it to be

A B
3700 Chestnut Pasadena
42 N. Hill Pasadena

 

[gbeard]

Can I reverse the cell so the city is first and then separate the cell based
on the length of the city name?

 

[Biff]

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

 

[Biff]

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

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

 

[Biff]

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

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

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

Can I reverse the cell so the city is first and then separate the cell
based on the length of the city name?

 

[gbeard]

Biff,
In the formula you wrote:

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

What does the "[" do?

 

[Biff]

Hi!

The [ character is used as a "marker". It marks the spot of the last
instance of the space character then instead of searching for a space, the
formula searches for [ since it is now a unique character as opposed to the
space character.

Not real clear is it?

Try this:

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

Counts the number of spaces in the string = 2

SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

Substitutes the 2nd instance of space with [ so that the string looks like
this to the formula:

3700 Chestnut[Pasadena

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

Returns the position of [ in the string = 14. This is the value used in the
LEFT function as to how many characters to return. Also, I goofed up in that
I should have subtracted 1 from that value because we only want characters
to the right of the 14th position and not including the 14th position, so:

FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1


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

Reduced to what it actually does:

=LEFT(A1,13)

Return 13 characters starting from the left in the string of cell A1.

Biff

 

[gbeard]

Biff,
Many thanks. I've seen that in different array's and never could find
information on it. Thanks again!

 

[gbeard]

Biff,
I'm getting a #VALUE! error with this array. I can't pinpoint the problem
though...any ideas?