Function to Subtract values with symbol within

[jneeb]

I work in the gas pipeline industry, and we use a different way of caluculating progress. For example 861+64 actualy means 86,164 feet. How can I make excel recognize this + value as nothing. I need to take 861+64 and minus another number such as 750+00.

[NBVC]

If the + is consistently at the 4th position, then try

=TEXT(REPLACE(A1,4,1,"")-REPLACE(B1,4,1,""),"000+00")

where A1 and B1 contain values to subtract from each other.

This will return result in same format as original.

If + is not consistently positioned, then try:

=TEXT(SUBSTITUTE(A1,"+","")-SUBSTITUTE(B1,"+",""),"000+00")

[jneeb]

If the + is consistently at the 4th position, then try

=TEXT(REPLACE(A1,4,1,"")-REPLACE(B1,4,1,""),"000+00")

where A1 and B1 contain values to subtract from each other.

This will return result in same format as original.

If + is not consistently positioned, then try:

=TEXT(SUBSTITUTE(A1,"+","")-SUBSTITUTE(B1,"+",""),"000+00")

I understand what you are saying but I do not know exactly how to do that. I am relatively new to excel, but I have a desire to learn.

[NBVC]

Just select one of the 2 formulas....the 2nd is probably better as it is more flexible....and copy it to your spreadsheet in the cell you want to see a result in.

You just need to change the A1 and B1 in the formula to reference the actual cells your first and second numbers are in...

[jneeb]

If the + is consistently at the 4th position, then try

=TEXT(REPLACE(A1,4,1,"")-REPLACE(B1,4,1,""),"000+00")

where A1 and B1 contain values to subtract from each other.

This will return result in same format as original.

If + is not consistently positioned, then try:

=TEXT(SUBSTITUTE(A1,"+","")-SUBSTITUTE(B1,"+",""),"000+00")

It works.
On the replacing the + sign when I get down to a lower value such as
86+00 I rplace the 4th position to the 3rd position and it works fine.
thanks for your help

[NBVC]

If you use the second function, i.e. =TEXT(SUBSTITUTE(A1,"+","")-SUBSTITUTE(B1,"+",""),"000+00")

then you don't need to do those adjustments....it is more flexible as to where the + sign occurs.

[jneeb]

If you use the second function, i.e. =TEXT(SUBSTITUTE(A1,"+","")-SUBSTITUTE(B1,"+",""),"000+00")

then you don't need to do those adjustments....it is more flexible as to where the + sign occurs.

Its perfect You just made my job a lot easier. We work a 12 hour day 6 days a week and there is just not enough time to do my data entry at the end of the day this will help tremendously. Thanks alot.