text to rows

  • Thread starter Thread starter Jeremy Smith
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top