Custom number format in Excel for error messages

[jdcrowley]

Hi everyone,

Is there a custom number format that will recognize whether a cell has an error (#NA or DIV0, etc ... ) and replace it with a text string?

I would like to prepare a table that looks nice. That means it can't have things like #NA in it. Errors have to be replaced with something pretty, like "...". But I don't want to type over my formulas with "..." because when I correct the input cell I will likely forget that I also have to go fix my table. I would prefer that Excel do this for me automatically.

I can do it with a formula that has something like =IF(ISERR(A15),"...", A15), but that is tedious to type into every cell. It would be much easier if I could just create a custom number format to solve the problem.

Thanks.

 

[Greg Truby]

Be prepared for tedium. Custom number formats to not address this and conditional formatting won't change the cell's content.

 

[barry houdini]

You can hide error values by conditional formatting using =ISERROR(a1) and applying a white font

 

[Greg Truby]

...shoulda mentioned that...

 

[jdcrowley]

Yeah, but I've gotta show the three dots in my table. Sounds like I am stuck. Thanks for the tips, though.

 

[erik.van.geit]

Hello, jdcrowley
Welcome to the Board !!!!

perhaps some code can do the tedious work for you
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the following code:

Option Explicit

Sub test()
'Erik Van Geit
'060126
'change formulas so they display "..." instead of errors

Dim cell As Range
Dim CellFormula As String

For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
CellFormula = Mid(cell.Formula, 2)
cell.Formula = "=IF(ISERROR(" & CellFormula & ")," & """" & "..." & """" & "," & CellFormula & ")"
Next cell
End Sub

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

if you do not want to change the entire sheet you can replace
ActiveSheet.Cells
by
Selection
select the range you want to change and run the code

ATTENTION
FIRST SAVE YOUR WORK
ONLY RUN ONCE on the same range

kind regards,
Erik

 

[jdcrowley]

Eric,

Thanks. Great reply. But a key thing that is missing that I need is that it has to change the "..." cells back to numbers once the input cells are corrected so that there are no more errors. It might be hard to write a macro that will put back the formulas once the errors have been corrected.

 

[jdcrowley]

Eric,

Sorry. I took another look and now I see. Your macro does the tedious work of putting in the iserr formulas. They do self correct when the error changes to a number. I initially thought your macro just typed "..."s in the error cells.

Awesome,

Thanks.

 

[Legacy 55362]

Here's a macro that I use, and can be run as many times as you like on the same cell(s).

Why do you need to change the formulas back?
Once the source data has been corrected the formula will not return "..."

Sub Add_ISERROR_To_Formulas()
Dim rng As Range, cell As Range, fmla As String
If TypeName(Selection) <> "Range" Then
    MsgBox "Select one or more worksheet cells."
    Exit Sub
End If
On Error Resume Next
If Selection.Cells.Count = 1 Then
    If Selection.HasFormula Then
        If Left(Selection.Formula, 8) = "=IF(ISNA" _
            Or Left(Selection.Formula, 11) = "=IF(ISERROR" _
            Or Left(Selection.Formula, 9) = "=IF(ISERR" Then GoTo e
        fmla = Right(Selection.Formula, Len(Selection.Formula) - 1)
        Selection.Formula = "=if(iserror(" & fmla & "), ""...""," & fmla & ")"
    End If
    GoTo e
End If
Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
If rng Is Nothing Then GoTo e
For Each cell In rng
    If Left(cell.Formula, 8) <> "=IF(ISNA" _
        And Left(cell.Formula, 11) <> "=IF(ISERROR" _
        And Left(cell.Formula, 9) <> "=IF(ISERR" Then
        fmla = Right(cell.Formula, Len(cell.Formula) - 1)
        cell.Formula = "=if(iserror(" & fmla & "), ""...""," & fmla & ")"
    End If
Next
e:
On Error GoTo 0
End Sub

NOTE :
This code does not handle array formulas properly. It will convert them to non-array formulas.
Post again if you need the code adjusted to handle array formulas.

 

[jdcrowley]

Why do you need to change the formulas back?
Once the source data has been corrected the formula will not return "..."

Correct. I don't need to change them back. I misunderstood the formula initially.

Actually, if I could change them back it would be a slight advantage since the next person who works on my table is going to freak out if they see that formula.