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

"David" wrote:

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

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


"David" wrote:

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

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

"David" wrote:

> 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 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.

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)




On Fri, 21 Nov 2008 12:11:01 -0800, David
<(E-Mail Removed)> wrote:

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