Date in A1, how to separate them into a2, a3 and a4....

[bt123123]

Hi
I have a list of data in a1, how to make them line by line in a2, a3, a4....

Below data is all in a1


0000027064 24.09.2012 1016.67- 0.00 1016.67- 92.57- 0.00 10142162 03.08.2012 38.28 6.79 31.49 3.48 0.00 1505 01.08.2012 5568.64 988.43 4580.21 506.24 0.00 1506 02.08.2012 224.40 39.83 184.57 20.40 0.00 1507 07.08.2012 1021.85 181.38 840.47 92.90 0.00 1508 07.08.2012 1104.07 195.97 908.10 100.37 0.00

I want:
a1: 0000027064 24.09.2012 1016.67- 0.00 1016.67- 92.57- 0.00
a2:10142162 03.08.2012 38.28 6.79 31.49 3.48 0.00
a3: 1505 01.08.2012 5568.64 988.43 4580.21 506.24 0.00
a4: 1506 02.08.2012 224.40 39.83 184.57 20.40 0.00
Thx

[Winon]

Hello bt123123,

You could use the Text to Columns feature. Results are shown in the attached WorkBook.

Also see the "Please consider" note at the bottom of this post,

[bt123123]

my situation is this...
all data in A1, cannot use text to column now.
Pls see attachment.

[bt123123]

Hi
I understand your way but I have a very long list date.

By doing your text to column first, it takes me forever to cut the data....

anyway thank you for your help

[Winon]

Hello bt123123,

In your original Post you say:

I have a list of data in a1, how to make them line by line in a2, a3, a4....

It would appear to me that you may not be fully comfortable utilising the Text to Columns feature, so I have automated it for you.

Based on your requirement:

I want:
a1: 0000027064 24.09.2012 1016.67- 0.00 1016.67- 92.57- 0.00
a2:10142162 03.08.2012 38.28 6.79 31.49 3.48 0.00
a3: 1505 01.08.2012 5568.64 988.43 4580.21 506.24 0.00
a4: 1506 02.08.2012 224.40 39.83 184.57 20.40 0.00

Click on the Button "Extract Numbers in A1" in the attached WorkBook, and everything will be done for you automatically.

You also state:

I understand your way but I have a very long list date.

If you could give us your exact scenario,about the very long list, and what else you would like to accomplish,we might be able to assist you further.

Also see the "Please consider" note at the bottom of this post.

[bt123123]

Hi
your macro is very useful for me.
My exact data is below.
But if my data is getting longer in future, then I need to have a lot of arracy in the macro "fieldinfo", isn't it?
THx

[Winon]

Hi bt123123,

Your "exact" Data in Cell A1, and the way in which you have changed it was done incorrectly. You cannot enter Data in any Cell to appear like rows of information, and expect Excel to "know" how to deal with it. For example, open the attached WorkBook, and see how the Data in Cell A1 should be entered.


In your first post, the data or numbers that were entered into Cell A1, was done correctly, and that Data can be manipulated in many different ways.

Now, if you could tell or show me what the Maximum content of Cell A1 could be, I could change the program for you accordingly.

As an illustration, I have done that for you in the attached WorkBook which can handle a series of up to 12 number combinations. (Once again, notice how the Data is enterd in Cell A1, before you Click on the "Extract Numbers in A1")

Hope that helps.