Use number portion only of a cell in a function?

[Leah]

Cell D85 is 6000 LBS

All I want to do is a simple multiplication: 6000 * .10

Really, my equation needs to be "number portion of D85" * .10

Is there a way to write this in a function?

[Emma_Fairclough]

Hi Leah,

Does =6000*0.10 not work?
What answer are you expecting to see?

Emma

[Leah]

The problem is that the cell is actually [6000 LBS] - all in one cell. How do I extract only the 6000 for my equation?

[Portuga]

Hi,

You can add this user defined function to your sheet:
^

Please Login or Register  to view this content.

Then use the following formula
=ExtractNumber(D85)*0.1

Good thing about this function is that you dont have to worry how many digits it has, or where is the number located within the cell, or if there are any leading spaces or end spaces.

[Emma_Fairclough]

Ahh sorry I misunderstood. As far as I can there isn't an easy way to do this (someone correct me if I'm wrong) but all I would do is extract the 6000 into a different cell and use that cell in the equation.
To extract it you could use a number of different text equations, e.g.

=LEFT(A1,4)
You would use this one if there will always be LBS and a space at the end of the number. It just counts backwards from the end of the cell however many characters you've told it to.
=MID(A1,1,4)
You would use this one if there will always be a four digit weight at the begining of the cell. It looks at the cell and the two numbers are the positions in the text you want it to return values between (so the value between the first and fourth characters)

I hope this has been of some help!
Emma

[Leah]

Thank you both!

[darkyam]

Emma,
Technically, Left counts forward from the beginning of the cell, rather than backwards from the end. =Left(A1,4) returns the same number whether A1 is 6000 or 600000. I would modify it slightly to =Left(A1,Find(" ",A1)-1)

[Emma_Fairclough]

Ah - You learn something new every day! Thanks