return numbers from a string

[Victtor]

each line of this data is in one cell. I need to extract:
1. the time frame
2. the number of customers served
3. the dollar amount for that time frame.

(please convert all to number format or currency format)

For instance, this is in cell B2: 20:00 - 20:14 4 $30.63 $7.66

so in C2 I would want 20:00 - 20:14
D2 I would want 4
E2 I would want $30.63
I dont really care about the last dollar amount but I guess for simplicity sake the $7.66 could go in F2

20:00 - 20:14 4 $30.63 $7.66
20:15 - 20:29 2 $17.10 $8.55
20:30 - 20:44 43 $273.70 $6.37
20:45 - 20:59 35 $225.08 $6.43
20:00 - 20:59 84 $546.51 $6.51
21:00 - 21:14 43 $265.27 $6.17
21:15 - 21:29 38 $224.77 $5.92
21:30 - 21:44 32 $196.37 $6.14
21:45 - 21:59 28 $172.11 $6.15
21:00 - 21:59 141 $858.52 $6.09

can someone please help me set this up?

 

[Ron Coderre]

With your sample data in A1:A10


These formulas, copied down, return the values you're looking for:

B1: =TRIM(LEFT(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*3))
C1: =--TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*3,LEN($A1)))
D1: =--TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*4,LEN($A1)))

These are the results in A1:D10

Col_A                               Col_B           Col_C     Col_D 
20:00 - 20:14 4 $30.63 $7.66        20:00 - 20:14       4       30.63
20:15 - 20:29 2 $17.10 $8.55        20:15 - 20:29       2       17.10
20:30 - 20:44 43 $273.70 $6.37      20:30 - 20:44      43      273.70
20:45 - 20:59 35 $225.08 $6.43      20:45 - 20:59      35      225.08
20:00 - 20:59 84 $546.51 $6.51      20:00 - 20:59      84      546.51
21:00 - 21:14 43 $265.27 $6.17      21:00 - 21:14      43      265.27
21:15 - 21:29 38 $224.77 $5.92      21:15 - 21:29      38      224.77
21:30 - 21:44 32 $196.37 $6.14      21:30 - 21:44      32      196.37
21:45 - 21:59 28 $172.11 $6.15      21:45 - 21:59      28      172.11
21:00 - 21:59 141 $858.52 $6.09     21:00 - 21:59      141     858.52

Is that something you can work with?

 

[Victtor]

Thanks! worked like a charm!