one column to three

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi
I have one column of data which goes, name; address; tel; every 3 rows.
I want to split it into 3 columns (one for each field).
My first thought was to somehow use paste special, transpose - but I
cant figure out a way to do it.
Any help appreciated
Andy Millar
 
Hi Andy,
Use text to columns with semicolon as the delimiter.

Select column A
Data, text to columns
choose the top choice of delimited
on the second dialog screen use semicolon

If you have a space after the semicolon you might
want to first replace "; " by ";" using Ctrl+H

You can remove leading and trailing spaces after the fact
with the TrimALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
 
David,

I might be doing it wrong - but I dont think thats quite what I need (I
probably didnt describe it clearly).

Currently I've got:

Column A row 1, 4, 7, n = name
Column A row 2, 5, 8, n = address
Column A row 3, 6, 9, n = tel

I want to create
Column B = Name
Column C = Address
Column D = tel

I'm now thinking along the lines of a macro to select every third row,
copy and paste it. Cant quite figure out how to select several rows at
the same time.

I'm doing this:
For rw = 1 To 300 Step 3
Cells(rw, 2).Select
Next rw
Selection.Copy
Range("C1").Select
ActiveSheet.Paste

but it just copies the last row selected, not them all

Maybe theres a simpler way than a macro?

Andy
 
How about a formula suggestion:

Put this in B1:
=OFFSET($A$1,(ROW()-1)*3+COLUMN()-2,0)
And drag to the right (B1:D1)
Then drag down until you run out of data.

Select B:D
Edit|copy
Edit|paste special|Values
delete column A.
 
Simpler than a Macro? Yeah, try this.

Column A - Column B - Column C - Column D
Name......... =a1...........=a2.............=a3
Address.........leave row 2 empty...........
Tel.................leave row 3 empty........
Name......... =a4...........=a5.............=a6
Address.........leave row 5 empty...........
Tel.................leave row 6 empty........

You can copy b1:d3 and then paste it down the column in one shot as
long as select a number of rows divisible by three - so that there is a
place for each copied cell and equal number of times.

Once you have all of the name, address, tel in columns b, c, and d.
Select the three columns. Copy the columns. Then Edit | Paste Special |
Values to replace the formulae with the data. Then you can sort it to
remove the blank rows.
 
Thanks for all replies --
both Dave Peterson's and comish4's work well - the use of Offset is
particularly clever!
thanks

Andy
 
Back
Top