asked on

Remove the number format in the sheet

Experts

I need to remove the number formatting applied to the sheet but only for the cells that have $- as a format.
example: I have a sheet that has many cells with $ - (meaning 0) and I want to avoid removing the format of each individual cell as that would be timely. I just simply want nothing in the cell (no currency sign or dash). I want to keep the cell coloring though.

thank you

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

You could use a Custom format. The syntax for a Custom Format is:

Positive Value; Negative Value; Zero

So for Standard Dollar formatting by clicking the Currency Format option you get:

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

By deleting the zero portion you get:

_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);

Thanks
Rob H

Rob Henson

BTW, this formatting is to be applied to all your currency cells, not just those where it is currently zero; the "blank" effect will then take effect whenever any of them become zero and will resume the full $0.00 format when not zero.

Thanks
Rob H

pdvsa

ASKER

Thank you. I have to say both experts had the same answer basically at least to me. ONe thing I noticed was that Subodh's currency symbol was close to the numbers and not sure what makes the format do that. I do like the currency symbol close to the number when dealing with a lot of numbers on a sheet.

Subodh Tiwari (Neeraj)

You're welcome. Glad to help.

My formatting is for Currency and Rob's formatting is for Accounting.

Rob Henson

Some points for Assistance would have been appreciated.

Thanks