Breaking up a line of text

[sungen99]

I am grabbing a line of data from a txt file online everyday. When I copy and paste into an excel document it obviously dumps into one big long line in A1.

The line looks like this:
4/29/16 DAILY TOTALS //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 5662 4420819 781 628462833.99 142.16

What i am looking for is something to break this up and report like this:

A2: 4/29/16
A3: 5662
A4: 781
A5: 142.16

As you can see I do not need all of the data just a few select parts. I can not tell you that the number of "/" will always be the same.

Thank you very very much for your help with this.

-Ken

[PFDave]

You'll need to use Text to columns to split this up however as / is the delimiter along with the date formatting you'd be best to use fixed width to decide how you'd like this split.

This will however break the text in to column not rows, but you can paste transposed after this.

You could also record a macro to use the / as delimiter then to concatenate the first 3 cells back together to create the date again, but this will all start from the use of text to columns but the DATA ribbon

[sungen99]

What would be the code or formula to break the line into a separate line for each word, number? If i can break it down like that i will be able to do the rest.

Thank you!

[Special-K]

With the value in A1
and A2
the following formula

=TRIM(MID(REPT(" ",LEN(A1))&SUBSTITUTE(A1," ",REPT(" ",LEN(A1)))&REPT(" ",LEN(A1)),A2*LEN(A1),LEN(A1)))

will return the A2th word, ie
if A2 is 2 the returned value is DAILY
if A2 is 9 the returned value is 142.16
Just adjust A2

If you set A2 to be COLUMN() you can copy the formula across a row

[AlKey]

Enter this formula in A2 and copy down

Please Login or Register  to view this content.

v	A
1	4/29/16 DAILY TOTALS ////////////// 5662 4420819 781 628462833.99 142.16*
2	4/29/16
3	5662
4	4420819
5	781
6	628462833.99
7	142.16

[sungen99]

WOW Alkey! fancy!!!!! and i works great.

Thank you all for the help!!!