Separate data into two columns

[Guest]

I have 2 lines of data in one column i.e., name and social security number.
Is there a way to automatically make that information into two columns.

Cheri

 

[Don Guillett]

have a look at
data>text to columns>pretty self explanatory

 

[Guest]

Yes, that works - I was wrong about my info tho - helping a co-worker - the
data is in two cells beneath each other. Can those be pulled out into two
colums. She receives a lot of data that comes to her that way and she wants
the social in the cell next to the name

 

[Guest]

Yes Cheri, just copy the two items
edit -past special -transpose - it should become a row wiwth the items in
separate columns

 

[Roger Govier]

Hi Cheri

Carry out the work on a copy of the data - just in case.
Assuming the data is in column A, start with A1 with a Name, A2 with a
SS No.
In cell B1 enter =A2
in cell B3 enter =A4
Mark the block of cells B1:B4 and use the fill handle (the small black
cross that appears as you hover over the bottom tight of the marked
block) by left click on the cross and drag down as far as required.

To "fix" the data, copy all of column B, place cursor in B1 Paste
Special>Values

Insert a new row 1 and mark cells A1:B1 Data>Filter>Autofilter
Use the dropdown on column B and Select (Blanks)
Mark the visible rows by selecting their row numbers and Delete

 

[Gord Dibben]

Cheri

Assuming your data is in Column A like

name
ss
name
ss
name
ss

In B1 enter this formula.

=INDEX($A:$A,(ROWS($1:1)-1)*2+COLUMNS($A:B)-1)

Drag across to C1 then drag B1 and C1 down untill you start returning zeros.


Gord Dibben MS Excel MVP