O
OnTheEdge
Anyone know of a function to paste comma delimited data that looks like this:
1,2,.3,4,5
into rows like this:
1
2
3
4
5
??
1,2,.3,4,5
into rows like this:
1
2
3
4
5
??
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Glenn
OnTheEdge said:Anyone know of a function to paste comma delimited data that looks like this:
1,2,.3,4,5
into rows like this:
1
2
3
4
5
??
First, paste the data into a cell. Then use Text To Columns with comma
delimited to split it into individual cells. Then select all of the cells and
Copy. Select the first cell where you want the rows to start and Paste Special
/ Transpose.
S
Sheeloo
How many such numbers are there?
If you are using Excel 2003 and you have less than 255 numbers then
Use Text to Columns to separate them into Columns
Select the numbers Copy and PASTE SPECIAL|Transpose on the row below
If you are using Excel 2003 and you have less than 255 numbers then
Use Text to Columns to separate them into Columns
Select the numbers Copy and PASTE SPECIAL|Transpose on the row below
D
Dave Curtis
Hi,
If you really need a single formula for this, then try the following.
Paste you data into, say, A1, so that it looks like 1,2,3,4,5,6,7,8 etc.
Then enter the following formula in A2
=--MID(SUBSTITUTE($A$1,",",""),ROW()-ROW($A$1),1)
and copy down as far as necessary.
Dave
url:http://www.ureader.com/msg/104241285.aspx
If you really need a single formula for this, then try the following.
Paste you data into, say, A1, so that it looks like 1,2,3,4,5,6,7,8 etc.
Then enter the following formula in A2
=--MID(SUBSTITUTE($A$1,",",""),ROW()-ROW($A$1),1)
and copy down as far as necessary.
Dave
url:http://www.ureader.com/msg/104241285.aspx
D
Dave Curtis
Hi,
I realised my previous suggestion only works if you have single digits
between the commas.
A much more elegant solution is,assuming the same as before, you use
=--WMID($A$1,ROW()-1,1,",")
and copy down
This uses Laurent Longre's WMID function (part of his free MOREFUNC.XLL
add-in, available at http://longre.free.fr/english)
Dave
url:http://www.ureader.com/msg/104241285.aspx
I realised my previous suggestion only works if you have single digits
between the commas.
A much more elegant solution is,assuming the same as before, you use
=--WMID($A$1,ROW()-1,1,",")
and copy down
This uses Laurent Longre's WMID function (part of his free MOREFUNC.XLL
add-in, available at http://longre.free.fr/english)
Dave
url:http://www.ureader.com/msg/104241285.aspx