Hi Everyone
I have some data in a cell in the form:
1 2 3 4 5
I want to move it to a column and each number goes to exactly one cell of the column.The final output should be like this:
1
2
3
4
5
The minimum number in my data is 0 and the Maximum is 100. Also there are spaces between the data.
I have tried to do it using "Transpose" in "Paste Special" but it did not work.
Any help in this regard is highly appreciated.
Best Regards
Last edited by virtualized; 04-26-2011 at 02:12 PM.
Try:
=IFERROR(0+TRIM(MID(SUBSTITUTE(A$1," ",REPT(" ",100)),1+(100*(ROWS($A$1:$A1)-1)),100)),"")
copied down. Where A1 contains the string of space separated numbers.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi
Thank you for the reply. The formula works to move data from Cell to column, but the spaces are not removed.
I have put the data in cell A1, and your formula in cell A2 and then dragged it down. The only problem is that it leave multiple spaces between the numbers which i don't need.
E.g the data in cell A1 is: 1 2 3 4 5
When i put the formula in Cell A2 and then drag down.
It puts 1 in cell A2 and then 2 in cell A5 and so on.
Can you please guide how the spaces can be removed.
Thanking You
Best Regards
This is what I get (see attached).
Hi
Can you please have a look at it now to know what i mean.
Now, there are more than one spaces between 1 & 2 and also 3 & 4, so you can see where 2 & 4 get placed (Please see the attachment).
Try:
=IFERROR(0+TRIM(MID(SUBSTITUTE(TRIM(A$1)," ",REPT(" ",100)),1+(100*(ROWS($A$1:$A1)-1)),100)),"")
copied down
Thanks a lot. The problem has been solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks