Display Blank cells as a dash (-) via Formatting

ashwinks

New Member
Joined
Sep 14, 2006
Messages
2
This might sound really strange, but I have an application that exports a table of numbers to Excel, and some of these numbers might be NULL or blank. I need to display a dash (-) in place of the blank cells without actually setting the value of the cell to a -. This is because I might perform calculations on these cells and cannot have non numeric values, although blank cells are acceptable. Now I need to set the format of these numbers IN THE APPLICATION to an excel format, so that they will carry over the format when the data is exported.

Can anyone advise me on any format string to display a blank / empty cell to display a "-" ? Any advice is greatly appreciated.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"Now I need to set the format of these numbers IN THE APPLICATION to an excel format, so that they will carry over the format when the data is exported. "

Custom number formats need something to format - not really designed to format blank spaces.

Formats applied aren't data, which is what is exported

So don't you just need a macro to run before you export that adds the dash to blank cells?

If so, post back with info about the range of data you're exporting / how you export...
 

ashwinks

New Member
Joined
Sep 14, 2006
Messages
2
I told you its gonna sound strange ! :)

Well, the application that I am using is MS Reporting Services, I have a report that has a table that shows summary numeric information, for some rows, the value may be NULL, and in such a case, the users want to see a "-" instead of an empty cell. This is for the web page. I got this working by using custom code to display either the number or a "-". But when they export to CSV, the column exports as a string, which is expected, since some of the rows contain a "-". But they dont want that... when exporting, they want the cells to be blank so as to keep the values as numeric.... I know, users.... ! what can you do ...

I would appreciate any advice. Thank you.
 

Forum statistics

Threads
1,136,878
Messages
5,678,301
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top