Display Blank cells as a dash (-) via Formatting

[ashwinks]

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.

 

[PaddyD]

"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]

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.