how to move all info in column a to seperate columns
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: Doe, John Title/Position: President Department: Department of Business Office Location: New York Email: How do i get name to column a, title into column b and so on? Thanks |
how to move all info in column a to seperate columns
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 --- "ksauey" wrote: 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: Doe, John Title/Position: President Department: Department of Business Office Location: New York Email: How do i get name to column a, title into column b and so on? Thanks |
All times are GMT +1. The time now is 09:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com