how to move all info in column a to seperate columns

[ksauey]

I am copying a phone/address directory and want to put it in columns.

This is how it looks when I copy it to excel:

Doe, Jane Ms.
Title/Position: Vice President
Department: Department of Marketing
Office Location: New York
Email: (e-mail address removed)

Doe, John
Title/Position: President
Department: Department of Business
Office Location: New York
Email: (e-mail address removed)

How do i get name to column a, title into column b and so on?

Thanks

 

[Max]

One easy tinker which might work out ok for you

Assuming data as posted is representative and runs in A2 down,
in groups of 6 lines per group (inclusive of the separating blank line)
with the colon ":" found in lines 2-5 of each group

Put in B2: =OFFSET($A$1,ROWS($1:1)*6-6+COLUMNS($A:A),)
Copy B2 across by 5 cols to F2
This will transpose the source data row-wise

Put in G2: =TRIM(MID(C2,SEARCH(":",C2)+1,99))
Copy G2 across by 4 cols to J2
This removes the "header" parts before the colon (inclusive the colon)

Select B2:J2, copy down until zeros appear in cols B to G, signalling
exhaustion of data. Then freeze all formulas by selecting entire cols B to J
and do an "in-place" copy n paste special as values. Clean up by deleting
cols C to G (and the source col A as well if desired). Job done.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik