one column to three

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 16 Jun 2005 11:22 GMT

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

Reply to this Message

David McRitchie - 16 Jun 2005 11:36 GMT

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> Hi
> I have one column of data which goes, name; address; tel; every 3 rows.
[quoted text clipped - 3 lines]
> Any help appreciated
> Andy Millar

Reply to this Message

Andy - 16 Jun 2005 13:01 GMT

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

Reply to this Message

comish4lif@verizon.net - 16 Jun 2005 15:59 GMT

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.

Reply to this Message

Andy - 16 Jun 2005 16:45 GMT

Thanks for all replies --
both Dave Peterson's and comish4's work well - the use of Offset is
particularly clever!
thanks

Andy

Reply to this Message

Dave Peterson - 16 Jun 2005 13:46 GMT

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.

> Hi
> I have one column of data which goes, name; address; tel; every 3 rows.
[quoted text clipped - 3 lines]
> Any help appreciated
> Andy Millar

Signature

Dave Peterson

Reply to this Message