Spread content of 1 cell over many cells

[Guest]

I just performed the "Text to Columns" function in Excel and it didn't do exactly what I was hoping for. I have a spreadsheet with 1300 addresses in the following format:

Street Address
City, State Zip

I want to change it so that Street Address is a separate column and City, State Zip is a separate column (ie: for a mail merge)

I tried using "Text to Columns" and it moved the top line (street address) to a new column, but left the adjacent column in the same format (as above). It's almost like it copied the top line and pasted it in a new column. I want it to actually split up the lines. Is there a way to do this?

Thanks in advance!

Cheri

 

[CLR]

Assuming your data is in comumn A, put this in B1:
=IF(ISNUMBER(LEFT(A1,1)*1),A1,"")

and this in C1:
=IF(ISNUMBER(LEFT(A1,1)*1),"",A1)

and copy both down as far as you have data in column A.........
This will leave your street addresses in column B and your City/state in
column C, but offset as they are in column A.

Then do Copy > Paste special > Values on columns B and C.

Then delete C1, moving all the cells in column C, up one row............

You will now have your Street addresses in column B and the respective
City/State in column C, both on the same row.........

Vaya con Dios,
Chuck, CABGx3

I just performed the "Text to Columns" function in Excel and it didn't do

exactly what I was hoping for. I have a spreadsheet with 1300 addresses in
the following format:

Street Address
City, State Zip

I want to change it so that Street Address is a separate column and City,

State Zip is a separate column (ie: for a mail merge)

I tried using "Text to Columns" and it moved the top line (street address)

to a new column, but left the adjacent column in the same format (as above).
It's almost like it copied the top line and pasted it in a new column. I
want it to actually split up the lines. Is there a way to do this?

 

[Guest]

I tried it again and now in C1 I get an error: #NAME?

I evaluated the formula and help displayed the following:

=IF(ISNUMBER(LEFT(A1,1)*1),"",A1) but the first A1 is underlined. If I continue with the evaulation, A1,1 becomes #NAME1,1 but italicized. This continues.

What am I doing wrong?

Dar

 

[CLR]

Hi Cheri...........

I just entered the following by hand:
A1 123 MyStreet
A2 MyCity, State
A3 345 YourStreet
A4 YourCity, State

That was my assumption of how your data was situated..........if not, then
this scheme won't work correctly.........

Next, I cut and pasted the below formulas into B1 and C1..........
B1 formula =IF(ISNUMBER(LEFT(A1,1)*1),A1,"")
C1 formula =IF(ISNUMBER(LEFT(A1,1)*1),"",A1)

then.........
B1 read "123 MyStreet"
C1 appeared blank

I then copied the formulas down columns B and C........

B2 appeared blank
B3 read "334 YourStreet"
B4 appeared blank
C2 read "MyCity, State"
C3 appeared blank
C4 read "YourCity, State"

You with me so far?
Now, do Copy > Paste special > Values on entire columns B and C.........this
removes the formulas and keeps only the data......

Now Right-click on cell C1 and choose "Delete", then choose "Shift cells
up", then OK

This should line everything up to read as follows:
B1 123 MyStreet
C1 MyCity, State
B2 appears blank
C2 appears blank
B3 345 YourStreet
C3YourCity, State
B4 appears blank
C4 appears blank

The only thing left to do is to highlight columns B and C and sort to your
preference to eliminate the blank rows.........

Hope this helps............if I passed too quickly over some part and you
don't understand or are still having toruble, post back and we'll look at it
again.

Vaya con Dios,
Chuck, CABGx3

I tried it again and now in C1 I get an error: #NAME?

I evaluated the formula and help displayed the following:

=IF(ISNUMBER(LEFT(A1,1)*1),"",A1) but the first A1 is underlined. If I

continue with the evaulation, A1,1 becomes #NAME1,1 but italicized. This
continues.