Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup info in one Column and then returning info in other columns | Excel Worksheet Functions | |||
Functions that will move info to a different location in column | Excel Worksheet Functions | |||
seperate first 2 lines of column in seperate columns in same row | Excel Discussion (Misc queries) | |||
HOW DO I SEPERATE INFO FROM ONE INTO TWO COLUMNS? | Excel Worksheet Functions | |||
Seperate Data in one column into two columns? | Excel Worksheet Functions |