text to rows

J

Jeremy Smith

I there a way to put text to rows instead of columns? What I am trying to
do is take a cell with reference designators separated by commas and copy
them to individual row.

ex.. C1, C2, C3, C4

to

C1
C2
C3
C4
 
P

Pete_UK

You want to "transpose" the data. Highlight the data in the row, then
click <copy>. Move cursor to where you want the columnar data to start
then Edit | Paste Special | Transpose (check) then OK and <Esc>.

Hope this helps.

Pete
 
P

Pete_UK

Just re-read your posting - didn't realise you had the data all in one
cell. Probably easier to use Data | Text-to-columns with comma as the
delimiter to separate the items into different cells in one row, then
do the transpose as described above.

Pete
 
S

SteveG

If you can remove the commas first you can use this. Say your string is
in A2 and you want them them in A3:A6. In A3

=MID($A$2,(ROW()-ROW($A$2)+1)*2-3,2)

This will return C1. Copy dowm to A6 and it will give you C2,C3,C4
respectively.

Does that help?

Steve
 
S

SteveG

If you can remove the commas first you can use this. Say your string is
in A2 and you want them them in A3:A6. In A3

=MID($A$2,(ROW()-ROW($A$2)+1)*2-3,2)

This will return C1. Copy dowm to A6 and it will give you C2,C3,C4
respectively.

Does that help?

Steve
 
O

Opinicus

I there a way to put text to rows instead of columns? What I am trying to
do is take a cell with reference designators separated by commas and copy
them to individual row.
ex.. C1, C2, C3, C4
to
C1
C2
C3
C4
Click to expand...

I've often had to do this. My solution till now (thanks Pete_UK for the
"transpose" tip) is to copy the cell into Word as text. Do a global find and
replace in which you replace all instances of "," with "^p" (carriage
return). Squeeze out initial " " (replace "^p " with "^p"). Then copy and
paste the result to where you want it in Excel.
 
Top