While 'wutzke' has already indicated he was after a VBA solution, I figured
if anyone wanted to use this as a basis for a different application, I would
re-post the formula accounting for newsreader's that break the long string
at blank spaces (making them hard to see). Here is that repost, broken apart
in such a way that blank spaces are preserved...

=IF($A1="","",MID(SUBSTITUTE($A1,LEFT($A1,1+SEARCH ("PM",$A1)),""),
1+FIND("|",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,LE FT($A1,1+SEARCH
("PM",$A1)),"")," ","|",COLUMN(A1))&" "," ","||",COLUMN(A1))),
FIND("||",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,LEF T($A1,1+SEARCH
("PM",$A1)),"")," ","|",COLUMN(A1))&" "," ","||",COLUMN(A1)))-
FIND("|",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,LEFT ($A1,1+SEARCH
("PM",$A1)),"")," ","|",COLUMN(A1))&" "," ","||",COLUMN(A1)))))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
But if you really do want a formula solution instead of a macro one, put
this formula...

=IF($A1="","",MID(SUBSTITUTE($A1,LEFT($A1,1+SEARCH ("PM",$A1)),""),1+FIND("|",SUBSTITUTE(SUBSTITUTE(S UBSTITUTE($A1,LEFT($A1,1+SEARCH("PM",$A1)),""),"
","|",COLUMN(A1))&" ","
","||",COLUMN(A1))),FIND("||",SUBSTITUTE(SUBSTITUT E(SUBSTITUTE($A1,LEFT($A1,1+SEARCH("PM",$A1)),""), "
","|",COLUMN(A1))&" ","
","||",COLUMN(A1)))-FIND("|",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,LEFT ($A1,1+SEARCH("PM",$A1)),""),"
","|",COLUMN(A1))&" "," ","||",COLUMN(A1)))))

in the first cell you want the first parsed out number to go in, copy it
across into the next 6 columns and then copy those 7 cells down as far as
you want.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Since you posted your question to the programming newsgroup, are you up
for a VBA macro program solution? Put this code...

Sub GetNumbers()
Dim X As Long
Dim Cell As Range
Dim Parts() As String
For Each Cell In Selection
Parts = Split(Cell.Value)
For X = 1 To 7
Cell.Offset(0, X).Value = Parts(UBound(Parts) + X - 7)
Next
Next
End Sub

in the code window for the sheet where your data is (right-click the
sheet's tab, select View Code from the popup menu and copy/paste the code
into the window that appeared). Then go back to the worksheet and select
all of the cells with your data (it's okay if there are non-data text
within the selection); press Alt+F8, select GetNumber from the dialog box
that appears and click the Run button.

Rick


"wutzke" wrote in message
...
In my worksheet I have a series of cells containing a text string. I
Would like split the text up into individual cells. The "05" is a
date. "PM" is a time code. Everything between "05" & "PM" is a
complete title.

All the rest after "PM" is seven numbers separrated by a space.

day location
05 BARHRJANS REGULAR1 PM 16.00 16.00 1.00 2.00 0.00 11.83 19.
05 BARHYTRFGHKNS REGULAR2 PM 1.00 1.00 10.00 20.00 0.00 121.83 129.
05 BARHHUNNY44S REGULAR3 PM 110.00 10.00 10.00 2.00 0.00 11.83 119.
05 BARHR887-GUNNS REGULAR4 PM 0.00 0.00 1.00 2.00 0.00 11.83 19.

The date code, "05" and time code "PM" never change in text length.
But the title length and the number vary in length.

=VALUE(MID(RIGHT(A1,LEN(A1)-SEARCH(" PM ",A1)-3),
1,SEARCH(".",RIGHT(A1,LEN(A1)-SEARCH(" PM ",A1)-3)))) will give me
the 1st value of 16 if I use the time code "PM" to search with and
then the decimal point "."

Is there a better way?