Need to convert the numbers with tolerances

J

Jambar

I have a column of numbers in teh format 0.45+_0.23 . +_ is a
character plus or minus .
How can I convert the number before and after +_ into mm
automatically ? Any formula ?

- Vijay
[email protected]
 
J

joeu2004

I have a column of numbers in teh format 0.45+_0.23   . +_ is a
character plus or minus .
How can I convert the number before and after +_ into mm
automatically  ? Any formula ?
Click to expand...

I presume the "numbers with tolerances" are actually in Text format.

Depends on the exact format of the number strings. If they are the 4
characters on the left and right, then:

=value(left(A1,4))
=value(right(A1,4))

If the length of the numeric strings vary, then:

=value(left(A1, find("+",A1)-1))
=value(right(A1, len(A1) - find("+",A1)))

where "+" is the plus-or-minus character that you mention.
 
R

Rick Rothstein \(MVP - VB\)

Your question is not entirely clear to me. Assuming you actually have +_ as your characters between the numbers, then...

Lower Number:
=LEFT(A10,FIND("+",A10)-1)-MID(A10,FIND("_",A10)+1,255)

Higher Number:
=LEFT(A10,FIND("+",A10)-1)+MID(A10,FIND("_",A10)+1,255)

If you are using this symbol ± instead, then use it in place of both the "+" and "_" in the above formulas. If the _ is really a minus sign, then use that instead of the _ in the above formulas.

Rick
 
J

Jambar

Hi Rick and Joe,

Both the solutions worked well for my problem.

Thanks very much for teh quick reply

rgds
Jambar
 
Top