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
pdvsaProject financeAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Apply the following custom number formatting to get the desired output...

$#,##0.00_);($#,##0.00);
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
pdvsaProject financeAuthor Commented:
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.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.

My formatting is for Currency and Rob's formatting is for Accounting.
0
 
Rob HensonFinance AnalystCommented:
Some points for Assistance would have been appreciated.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.