Formula results in an ERROR

[jgarland]

Hi all, I've tried every way I know to correct the following error, including numerous internet searches, but I just can't figure it out.

Example:
cell A1 contains a formula that evaluates to the number 5 (could be any number),
cell B1 contains a formula that evaluates to the number 7 (again, could be any number).
cell C1 contains the formula A1+B1, which results in the number 12. So far, so good.

But when either one of the formulas in cells A1 or B1 evaluates to zero, I get #VALUE! for the result in C1.

I can't figure this out. If A1 formula Results in zero, and B1 formula results in 7, then C1 should read 7.

 

[jgarland]

But you didn't answer his question.
Also you have not answered my question regarding your version of Excel. Nor have you updated your profile to show it.

Look at the bottom of my post, you will plainly see my version of Excel is listed. Also, I was not sure how to answer his question about evaluating either to zero or an empty string, that is why I added additional information, which he clearly picked up on.

Are you having a bad morning Fluff? Why so combative. I wish you well Brother.

 

[Fluff]

Whilst you have added your version to your signature, most members look at your profile, which is why I asked you to update that.

 

[jgarland]

In my formula I turned the original string into all upper case so then only the upper case substitutes are required.
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(0&UPPER(E13),"R",""),"L",""),"H",""),"I",""),"O",""),"S",""),".",""),"""",""),",",""),":","")

You can see in my mini sheet above that with the shorter formula both upper and lower case have been removed.
Did you try the formulas I suggested?

I will try the new formula immediately, thanks for your help, it is much appreciated.

 

[Peter_SSs]

There is nothing about having a bad morning. You are simply being prompted to answer this question that had been asked of you way back in post #3.

What does
=SUM(A1:B1)give you?

 

[MARK858]

which he clearly picked up on.

???? I haven't responded until now.

My 1st question was when you evaluated the formulas was it an actual 0 returned.
My 2nd question was what did a different formula result in (as @Peter_SSs states in the previous post), this I am also waiting for an answer to

 

[jgarland]

In my formula I turned the original string into all upper case so then only the upper case substitutes are required.
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(0&UPPER(E13),"R",""),"L",""),"H",""),"I",""),"O",""),"S",""),".",""),"""",""),",",""),":","")

You can see in my mini sheet above that with the shorter formula both upper and lower case have been removed.
Did you try the formulas I suggested?

Success!! It works great. Thank you so much, you have just made my day a little better!!!

 

[jgarland]

oops, sorry, I quoted the

???? I haven't responded until now.

My 1st question was when you evaluated the result was it an actual 0.
My 2nd question was what did a different formula result in (as @Peter_SSs states in the previous post), this I am also waiting for an answer to

Oops!! I quoted the wrong post, my apologies to both you and Fluff. All has been resolved. Thanks to all who participated.

 

[Peter_SSs]

My 2nd question was what did a different formula result in (as @Peter_SSs states in the previous post), this I am also waiting for an answer to

I will answer it for you. The A1 and B1 formulas were given in post 2 and they must return text, even if the target cell was originally a number, empty, text (anything other than an error). Therefore the SUM formula will return 0. The original A1+B1 formula would coerce "numerical text" values (but not "" values) to numbers but the SUM would not.

Success!! It works great. Thank you so much, you have just made my day a little better!!!

You are welcome. Thanks for the confirmation.

 

[MARK858]

I will answer it for you.

Thank you for your response but it was asked to the OP (and is irrelevant now as the OP has an answer they are happy with)