Numerical list...in a cell. How to distribute to rows?

[3G]

Hi there!

Lots of great threads going on right now. I'm learning a ton!!


Of course, I have a problem though. I was given a spreadsheet with multiple numeric entries in one cell like this:


19296

19297

77786

77787


Some have only 2 entries, others have 15. What I'm trying to do is pull each entry out into its own row. I've tried using the Chandoo technique with a formula like this:

=MID($D$57,E57,IF(ISERROR(D2),99,F57-E57)), where E57 has the value of FIND(" ",1) in my list, but, there are no spaces for it to consider. One consistency though is that every value is only 5 digits long. Each row seems to be separated/created by a carriage return of ALT+enter.


Any input is greatly appreciated.


thx

 

[Luke M]

Easiest way would be to do Text-to columns, and then use Paste Special - Transpose to get the data into multiple rows. Now, how to break on the line feed:


Step 1) select the column

Step 2) go into text to column

Step 3) tick other and in the box type 0010 while Holding the Alt key. (it looks as if youve typed nothing, dont worry)

Step 4) hit next and finish


If you want to stick with formula version, try searching for CHAR(10) like so:

=FIND(CHAR(10),D57)

 

[3G]

Luke...that's awesome! Thanks man! Love it!!!


Great idea on the Transpose too.


Thanks man!!!

 

[shrivallabha]

Hi 3G and Luke,


Easier method for doing "ALT + 0010" is using shortcut "CTRL + J".