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]

I need to clarify the above because my exact formulas may make a difference.

Cell A1 formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E13,"R",""),"r",""),"L",""),"l",""),"H",""),"h",""),"I",""),"i",""),"O",""),"o",""),"S",""),"s",""),".",""),"""",""),",",""),":","")

Cell B1 formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F13,"R",""),"r",""),"L",""),"l",""),"H",""),"h",""),"I",""),"i",""),"O",""),"o",""),"S",""),"s",""),".",""),"""",""),",",""),":","")

Cell C1 Formula:
A1+B1

 

[MARK858]

Are they actually evaluating to zero or an empty string?
What does

=SUM(A1:B1)

give you?

 

[jgarland]

Emp

Are they actually evaluating to zero or an empty string?
What does

=SUM(A1:B1)

give you?

Hi MARK858, thanks for responding.

The formulas are looking for any number in a string. For instances: "Bill 17". I am trying to pull just the number from the string. A & B work perfectly, they are pulling the numbers from the strings. C will throw the error if either A or B does not return a number.

Note that I can't use different cells to separate the "Bill" from the "17" because of other constraints in the workbook, it all has to be one line.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, you haven't answered Mark858's question.

 

[Peter_SSs]

C will throw the error if either A or B does not return a number.

Would it be okay to return the number zero if the existing formula does not return a number?
Also. you can eliminate quite a few of those 'SUBSTITUTES'
Would something like this suit you?

25 01 08.xlsm
	A	B	C	D	E	F
1	234	0	234
11
12
13					Hill234	R:h
Formula

Cell Formulas
Range	Formula
A1:B1	A1	=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(0&UPPER(E13),"R",""),"L",""),"H",""),"I",""),"O",""),"S",""),".",""),"""",""),",",""),":","")
C1	C1	=A1+B1

 

[jgarland]

Returning zero would be OK. As for the number of 'SUBSTITUTES' I was under the impression I needed one for both Upper & Lower case letters.

 

[jgarland]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, you haven't answered Mark858's question.

I am aware of that. I was away from my computer until this morning when I saw the reply. Also note that I did reply to his initial response, see above your reply.

 

[Peter_SSs]

As for the number of 'SUBSTITUTES' I was under the impression I needed one for both Upper & Lower case letters.

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?

 

[Fluff]

Also note that I did reply to his initial response, see above your reply.

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.