Excel : ignore letters in cell during calculation 4

[Guthro]

Assuming cell a1 = 40 and b1 = 70cl
How can I make a calculation with a result in c1 using the figures in a1 and b1 but ignore the letters "cl" in b1 ?

Thanks.



My Feeblegirl.com Forum boards for mmorpgs, sport, fun, politics...

http://www.jediknightsforum.com

 

[Gavona]

Given the limit range of units you are likely to encounter another way would be to use substitute function to remove the unit of measure:
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"ml",""),"cl",""),"l","")))

You could also use Edit, Replace to remove the unit of measure. Or to replace "cl" with " cl etc". Then use text to columns to split the number from the units as suggested by lionelhill.

Gavin

 

[lionelhill]

A star to Gavona, because his solution works even better than you'd think. If you substitute the exponentials for the appropriate units, his version can also convert your values to numbers in ml.

=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"ml",""),"cl","e+02"),"l","e+03")))

 

[lionelhill]

except it helps if you use the right exponent for cl... Sorry.