Formula to change a cell with - to 0

[fostertiname]

Hello Excel gurus! I know there's some formula that will find all cells with - and make it a zero. Currently when I excel from Q.Books, the result of any GL account that is zero will show up as "-". I need to change that to a zero. Don't think I'd be able to just use "replace", right? When I tried to, because the cell is not set up as General, it wasn't working for me...

 

[MUKESHY12390]

why you just don't formatting....?

 

[joeu2004]

The cells are probably formatted as Accounting. With the Accounting format, "-" is used to indicate zero.

You can customize the Accounting format. Select cells, right-click and click Format Cells. Click the Number tab, then click Custom. You will probably see something like the following:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Change to format to one of the following, depending on your intent

_($* #,##0.00_);_($* (#,##0.00);_($* 0??_);_(@_)

or

_($* #,##0.00_);_($* (#,##0.00)

depeding on how you want zero to appear.

 

[fostertiname]

Yes, I did that. I went through each cell one by one to change the formatting but that took forever

 

[fostertiname]

The cells are probably formatted as Accounting. With the Accounting format, "-" is used to indicate zero.

You can customize the Accounting format. Select cells, right-click and click Format Cells. Click the Number tab, then click Custom. You will probably see something like the following:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Change to format to one of the following, depending on your intent

_($* #,##0.00_);_($* (#,##0.00);_($* [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]0[/COLOR]??_);_(@_)

or

_($* #,##0.00_);_($* (#,##0.00)

depeding on how you want zero to appear.

Sweet, let me give that a whirl. It's nice to be in the presence of such talent. Thank you!