How do i get the values in different rows in excel using formuale?-CodePudding

I have a string of numbers which are spaced randomly in a single cell like this -:

"8  29  30 36  32  16 16 33  36 30  1  32 1  2  0  4  21  11  35 17  419 47  14 13  30 36 35 23 27 14  35 20 36 28 44  16 4 4 32 22 0  28 16 27 27 27  30 12 10 34 8  17 19 1 24 17  8"

I want this to put individual numbers in different rows starting from 1 to n (total numbers) using a generalized formuale but not able to write the formulae. Could anyone help me please?

Any help would really be appreciated as I am not able to do this!

Thanks and Regards,

CodePudding user response：

You can do this by selecting the cell with the numbers, navigating to the Data tab, and selecting "text to columns". Select "Delimited" in the window that pops up and choose next. then select "spaces". that should work!

CodePudding user response：

There is no need to use VBA at all, the following formula will work.

Assuming the string you mention is in A1, put the following formula into E4:

=FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(A1,"  "," ")," ","</y><y>")&"</y></x>","//y")

It's a bit of a hack, but it works. There are two elements. Firstly, I notice that you have some numbers separated by double-spaces and I assume that's just padding. Those are removed using the SUBSTITUTE(A1," "," "). Secondly, the result is then altered, using SUBSTITUTE - changing each space into XML tags which are then parsed using FILTERXML into an array.

If you're using Office365, then you could use the purpose-built TEXTSPLIT function instead.