CODEWITHSUNDEEP
excelexcel-formula
Bryan Homer
Bryan Homer

Reputation: 1

excel error replace #VALUE

I receive page numbers in an email and have been successful at using the split function to get them into excel but my problem is sometimes they come in with an S in front of them Here is how they will come in: S55-65 55-65

now i can split them further and I have 2 columns

If they come in as S55-65

      A         B
1    S55       55
2   -65       -65
3  #VALUE!     10

For column A I add the 2 together to get the total number of pages but since it has an S the value is #VALUE!

For column B the value is 10

If they come in as 55-65

      A           B
1   S55        #VALUE!
2   -65       -65
3    10        #VALUE!

B1 is an error because it uses a split to get rid of the S

In a separate cell I would like a code that would allow for the correct answer to show if either A3 or B3 show #VALUE!.

I have tried using =IF(ERROR.TYPE(A3) = 3,B3,A3) but I cannot get that to work

Upvotes: 0

Views: 210

Answers (2)

Pit J
Pit J

Reputation: 179

Here my example

=IFERROR(CHOOSE(F7,"Acve","Red","Se","InvO","PO","Susactive","Suspense Other","Del","I223","Penng","PEO Cond","Invesyed"), " ")

Excel

enter image description here

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

If you have a more recent Excel version, you can use IfError():
=IfError([Formula],[what to do if the formula returns an error]).

If you have an older Excel without that function, you can use a combination If() statement: =If(IsError([formula]),[what to do if error],[formula])

Or

=If(IsErr([formula]),[what to do if error],[formula])

The difference in IsError and IsErr is (from Microsoft)

ISERR - Value refers to any error value except #N/A.
ISERROR - Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

You can also just check for #N/A error with IsNA().

Upvotes: 1

Related Questions