1

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?

  • 1
    There is no cell format part applied to empty value. – Akina Dec 7 '19 at 10:41
  • 1
    You cannot. You could use a VBA event-triggered macro, though. – Ron Rosenfeld Dec 7 '19 at 12:09
  • @Akina Ok. Then how can I show 0 in a number column/cell, whenever it is Blank or Zero? – anand Dec 8 '19 at 11:31
  • @RonRosenfeld Same query as to Akina. – anand Dec 8 '19 at 11:32
  • 1
    A formula can only alter the cell in which it is located. So, without VBA, you will either need to have the contents of every cell dependent on the formula within that cell, or do a Find/Replace over the area to replace all blank cells with zero's. (Method 1 or Method 2 of Rajesh) – Ron Rosenfeld Dec 11 '19 at 11:09
2
  • 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))

|improve this answer|||||
  • Although your solution may be acceptable, you should point out that your solution changes not only the appearance (as would be done by formatting) but also the value of the cell. If the OP is using formulas that rely on the cell actually being empty, your solution may cause unexpected results. – Ron Rosenfeld Dec 8 '19 at 20:38
  • @RonRosenfeld,, thanks for the observation,, I guess that U are trying to point out Method 4,, since OP is struggling with Zero to replace with dash therefore the suggested formula only focus on that,,, since OP has not suggested any sample data therefore difficult to think about another possibilities. – Rajesh S Dec 9 '19 at 4:32
  • @RajeshS actually my main concern is with Blanks in a cell. For zero my the custom format mentioned works fine. Even in cases of cells which has a 0 value, if I delete it, it goes back to going Blank instead of a "-" hence the issue. So an applicable solution would have been to replace the 0 and Blank both with "-". But as pointed out by the other users, its not possible. – anand Dec 10 '19 at 5:38
  • @anand,, I think that still you have not tried any of the suggested method better try what I've been suggested instead of other's view point,, what I've suggested ,, have been tested for the job, before I've posted them as answer. You may use Find & Replace or the Formula I've suggested,, also the VBA macro. Better you use the Macro, let me modify the post,, it will works for both BLANKs & Zeros !! – Rajesh S Dec 10 '19 at 7:09
  • @anand,, now I've modified the MACRO (Method 3),, read carefully the instruction and RUN it, will works for both Blank as well for Zeros!! – Rajesh S Dec 10 '19 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.