How to do texttoColumn delimited by space in column?

Hi,

Column A in worksheet1 contains multiple numbers or texts separated by space.
I am trying to take all the data on column A (which were copied from someother text file) put them in to different cell vertically ( in column).
I've tried to do text to column delimited by space and transpose it afterward.
The problem is I get this warning sign when I try to do text to column delimited : "text extends beyond the worksheet. some text will be lost"


Is there any way to write VBA that checks if first character on column A and row i (on sheet1) is either a number or asterisk (*), if so take that cell and do text to column delimited by space in vertical on Sheet2.


Column A looks like :
Row 1) H20
Row 2) 1 2 3 4 5 6 7 8 9 10 11 2 3 3 5 6 6 7 8 9 0 0 7 ...
Row 3) C02
Row 4) 12 432 5 7 54 76 89 4532 987 43 8973 48 3 7834 983 49 3489 34 9 34 ...
Row 5) Temperature
Row 6) 123 231 23 23 31 34 4 45 56 67 75 78 68 689 68 76 35 243 413 ...

and so on...


Desired output: (On sheet2)
Column A | Column B | Column C
H20 | C02 | Temperature
1 | 12 | 123
2 | 432 | 231
3 | 5 | 23
4 | 7 | 23
5 | 54 | 31
. | . | .
. | . | .
. | . | .
etc ...

example attached:
example.xls

Please take a look at the example xls file.

Any help would be appreciated.
Thanks,

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.