convert text format to number

[mwb]

I am trying to convert existing data from text to number format. the cells contain numbers and text ((123.100grams). The text is always different as is the number of decimal places that need to be retained. Is there a way to pull the number of entered decimal places (i.e. 123.100 = 3) from this string? the Val function will return 123.1 but I loose the displayed decimals if I rewrite the cell Val after changing the format to number. I think I can calculate it by finding the position of the decimal, then finding the position of the last number... not sure how to find the last number though.

Cheers!

 

[Mark W.]

=--"1" produces 1

You can think of it as...

=-(-("1"))

Duel unary minus (-) operators can be used to coerce a text value into a numeric one.

In fact, any arithmetic operator can be used for this coercion; however, if one uses a binary operator (e.g., +, *) it's best to use it in conjunction with the operation's identity element (0 and 1 respectively).

For example,

="1"+0 produces 1
="2"*1 produces 2

Barry's formulation is using coercion to change the text characters produced by the MID function into numeric values so that he can use an approximate MATCH to find the last numeric value in the string. This nifty approach works because 10 is larger than any single digit number.