Need to convert the numbers with tolerances

[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
(e-mail address removed)

 

[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 ?

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.

 

[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

 

[Jays]

Yes what you have tried to say is not entirely clear - perhaps an IF
statement?

 

[Jambar]

Hi Rick and Joe,

Both the solutions worked well for my problem.

Thanks very much for teh quick reply

rgds
Jambar