formatting

  • Thread starter Thread starter johnrb7865
  • Start date Start date
J

johnrb7865

Hello, does anyone know how I can look at a cell without seeing a special
character? I need to determine a length by subtracting one cell from another
but the numbers in the cell are engineering stationing in the format
xxx+xx.xx (300+45.10). I need to use this data to get the number 30045.10 to
then subtract from another cell of the same type.
 
With your sample entry in cell A1...
Try this:

B1: =--SUBSTITUTE(A1,"+","")

If A1 contains 300+45.10
that formula returns 30045.1

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Another thought, using the same approach to calculate differences.

With
A1: 300+45.10
A2: 200+29.40

This formula removes the plus signs and returns A1 minus A2.
A3: =SUBSTITUTE(A1,"+","")-SUBSTITUTE(A2,"+","")

In the above example,
A3 returns: 10015.7

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
That worked perfect. Thanks, John

Ron Coderre said:
Another thought, using the same approach to calculate differences.

With
A1: 300+45.10
A2: 200+29.40

This formula removes the plus signs and returns A1 minus A2.
A3: =SUBSTITUTE(A1,"+","")-SUBSTITUTE(A2,"+","")

In the above example,
A3 returns: 10015.7

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Click to expand...
 
Back
Top