How to custom format excel cell containing 0 or BLANK value to -?

Question

I want to format cells on a column of an excel(with general or number values). So if a cell had a 0 and I would like to replace with a "-". Hence I use the custom format *

(* #,##0);(* (#,##0);(* "-"??);(@_)

*

But if a cell has 0 or BLANK value, how do I modify the above format to also show "-" for BLANK values?

Answer 1

• For 1st part of your problem, the Cell format you are trying can be used, also this may be used.

  0;-0;—;@

• As per my knowledge to replace BLANK with Dash, Excel doesn't provides any Custom Cell Format, but yes there are few other methods.

---

Method 1.

• Select cells, press CTRL+G.
• Select Blanks option.
• Type -.
• Finally press Ctrl+Enter.

---

Method 2:

Select required cell range.

• Press Ctrl+H
• For Find what leave the field empty.
• For Replace with type -.
• Finally press Replace all.

---

Method 3:

You may use this VBA macro as Module.

Sub ReplaceBlanks()

Dim Rng As Range
Dim WorkRng As Range

On Error Resume Next
xTitleId = "Replace Blanks & Zeros"

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

For Each Rng In WorkRng
     If Rng.Value = "" Or Rng.Value = 0 Then
        Rng.Value = "-"
    End If
    Rng.HorizontalAlignment = xlCenter
Next
End Sub

N.B.

This fills all cells contains blank & Zeros in the selected range with Dash sign.

• Either press Alt+F11 or Right click the Sheet TAB and hit View Code, to open VB editor.
• From Insert menu hit Module.
• Copy & Paste this code.
• Save the Workbook as Macro Enabled.
• Finally RUN the macro.

---

Method 4:

• Suppose you are getting ZERO after applied a formula.

=IF(SUM(A1:E1)=0,"-",SUM(A1:E1))