Convert rows to columns

[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?

 

[David]

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

 

[porter444]

Take a look at "Edit", "Paste Special", and choose "Transpose".

There is also a utilty pack you can download that has a "transform" macro.
Good news, there is a free trial version. See information here:
http://spreadsheetpage.com/index.php/pupv6/utilities

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott

 

[Shane Devenshire]

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

 

[MyVeryOwnSelf]

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.

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 B11 and copy down for as many rows as needed.

Modify as needed.

 

[Chip Pearson]

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)