Extract each alphanumeric sequence between spaces in a large text string

[NMB311]

This is an excerpt from an attendance report I use at my job and I'm trying to make it easier to understand. I'm trying to sort through these large strings of data by extracting each name, number, date, etc. and placing it into separate cells. I've done a lot of it using the left, right, mid, and find functions.

I’ve been able to extract the first and last names using this formula:

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))&LEFT(A1,FIND(" ",A1)-1)

last first 9999992 002020 11/18/08 08/23/11 TUE ABS CONFIRMED 7.25 c/o - other job (this whole string is in cell A1)

So that they will display as “First Last” in the cell where I put that formula. The logic that I used was to find the spaces and use them as reference points for the mid formula. But it seems way too complicated to do that past the first two spaces in the string.

So, my first question is, is there an easier way to extract each alphanumeric sequence between the spaces in the text strings?

last first 9999991 001010 11/28/07 08/22/11 MON ABS CONFIRMED 4.50 c/o sick
last first 9999993 001020 09/22/09 08/21/11 SUN ABS CONFIRMED 5.25 c/o / concussion

And, my next question is, if the above question isn’t possible, what I need most is to get the name, the dates, and the last numbers isolated (i.e. 4.50 and 5.25). I especially can’t figure out an easy way to get the last numbers out because the text following doesn’t follow any particular pattern. The number of words varies from 1 to 5 sometimes.

Hopefully that is all understandable. Thanks in advance!

 

[jfish1288]

It sounds like the easiest way to do this is not by using a formula but by using the "Text to Columns" function. In Excel 2002 this is found under the "Data" tab. You can seperate the text into seperate columns based a fixed with or based on tab, space, semicolon, comma, or a character you choose.