Convert rows to columns

  • Thread starter Thread starter David
  • Start date Start date
D

David

Similar to the "convert columns to rows" question, I have a mass of data in
this format:

Name
Address
City, State, ZIP

I need to convert this info into columns so I can then import it into my
contact management program.

Any thoughts?
 
I forgot to mention that I have thousands of these entries and a batch
process would be helpful (versus a one by one solution).

David
 
Hi,

Suppose your data is in column A1:A5000 starting in an empty column enter
the following formulas, here I will use C1
C1 =A1
D1 =B1
E1 =LEFT(A3,FIND(",",A3)-1)
F1 =MID(A3,FIND(",",A3)+2,FIND(",",A3,FIND(",",A3)+2)-FIND(",",A3)-2)
G1 =MID(A3,FIND(F1,A3)+4,10)

1. Now highlight C1:G3 (yes G3)
2. Drag the fill handle down as far as you data goes
3. With the entire range selected press Ctrl+C (copy)
4. Choose Edit, Paste Special, Values

5. With the entire range selected press F5, Special, Blanks, OK
6. Press Ctrl+- (Control and Minus), respond shift cells up.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire
 
I have a mass of data in this format:
Name
Address
City, State, ZIP

I need to convert this info into columns so I can then import it into
my contact management program.
Click to expand...

Here's one way, assuming the addresses are an A1:A3, A4:A6, A7:A9, etc.

In B1, put
=OFFSET($A$1,3*ROW()+COLUMN()-5,0)
Then copy B1 across to D1.
Then select B1:D1 and copy down for as many rows as needed.

Modify as needed.
 
You can use some formulas to create a single row of data out of the
"block" of 3 lines per address. The formulas below assume that your
data list begins in B3 and that the name will be split into F3,
Address into G3, City/State/Zip into H3.

In F3, enter

=OFFSET($B$3,(3*(ROW()-ROW(F$3)))+(COLUMN()-COLUMN($F3)),0,1,1)

Fill this across to fill F3:H3. Then, to split apart the
city/state/zip field in H3 into 3 columns, enter

=LEFT(H3,FIND(",",H3,1)-1)
in I3,

=MID(H3,FIND(",",H3,1)+2,FIND(",",H3,FIND(",",H3,1)+1)-FIND(",",H3,1)-2)
in J3

=MID(H3,FIND(",",H3,FIND(",",H3,1)+1)+2,99)
in K3.

Now, select cells F3:K3, select down the rows of the sheet for as many
"blocks" of addresses you have, and Fill Down from the Edit menu.

Now, you'll have all elements in separate columns.

See http://www.cpearson.com/excel/ColumnToTable.aspx for more
information.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Back
Top