Excel 2007 Help with formula for data from 1 cell to other cells etc

Hi all again,
I have 10,000 records and I need a certain cell formula to extract part text from an adjacent cell and place in new cells with calculations also added for all records for example:

Cell A1= "329.5m | 2h 35m" whereas 329.5m is the mileage and 2h 35m is the time taken. (note: this is the original text supplied by google maps)

Now I would like to:
1. put the first part "329.5" in an adjacent cell of B1 with text formatted to number (i.e. 329.5m = 329.50), then in C1 a calculation applied to B1 of: x2.0 and then x0.17 (i.e. to work out fuel cost)
2. put the second part "2h 35m" in an adjacent cell of D1 with text formatted to number (i.e. 2h 35m = 2.35), then in E1 a calculation applied to D1 of: x2.0 and then x30.00 (i.e. to work out labor cost)

Can this be achieved please
Many thanks in advance

Put this in B1:

=LEFT(A1,SEARCH("m",A1)-1)*1

and this in D1:

=LEFT(SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("| ",A1)-1),"h ","."),LEN(SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("| ",A1)-1),"h ","."))-1)*1

I don't understand what you want in C1 and in E1.

Hope this helps.

Pete

Thank you pete but the formula you gave comes up with "Value" in B1
As for your last question, I would like the value of B1 multiplied by 2 and again by 0.17 and the result in C1 to work out fuel cost, also the same for D1, except multiplied by 2 and again by 30.00 and the result in E1 to work out labour cost.
Cheers for now

You might have a space in A1 that your quoted example doesn't include - please post an example workbook so I can see the data directly.

As for the other two items, you can have a formula like this in C1:

=B1*2*0.17

and in E1:

=D1*2*30

although I don't know why you need to multiply by the two numbers - these are the same:

C1: =B1*0.34
E1: =D1*60

Note that 2.35 in D1 is not the same as 2hrs and 35 minutes - I think you want 2.58 as a result.

Hope this helps.

Pete

Book2.xlsxI think your close to what I require :>)
I have attached a example work book of what I am trying to achieve
Many thanks Pete

It would help if you gave the correct row and column references for your data.

The attached file shows the required formulae to do the transformations in C2 and D2.

Hope this helps.

Pete

Pete, your a star.
Thank you so much for this, it's perfect. I have been dabbling for 5 days trying to work it out.
Much appreciated, I will remember you for the future.

Pete, if you are still free, can you take a look at the excel workbook attached and as you will see the info I displayed in column B was incorrectly inserted by me.
Could you please re-adjust the formulas to work with this book attached and with Column "B" be now edited correctly by me?
Sorry to be a pain but your expertise is required again.
Book1.xlsx
Best regards
Steve

See attached file.

Some contributors get very irate when the OP changes the requirements - next time, ensure that you post the correct formats in the first instance.

Pete

Sorry about that Pete, but thank you again for your help and time.