Handle Text / Numeric values in SSRS while Export To Excel

[achuki]

I am using SSRS and one of the column in my report is having both Text values and Numeric values.
e.g.


A
U
1
2
Y


While doing export to excel, for numeric values there is a TAG coming in the cell (excel file) with message "The number in this cell is formatted as text or preceded by an apostrophe."
How can I remove the TAG and the message for numeric values.

[macropod]

Hi achuki,

Is there a reason you can't simply remove the tag for all records (eg by Find/Replace)?

[Catalin.B]

In an empty cell, type 1 (this will be the multiplication factor used below, using 1 will not change the cell values)
Right click the cell, select copy, then , after selecting te column, from the paste special menu, choose operation Multiply--> ok. Press Esc from keyboard to exit CutCopyMode. All column will be reformated to General format, which means that the numbers are alligned to the right side of cell, and text is alligned on the left side of the cell. At this moment, the numbers from that column are recognised as numbers, not text, and can be used in another calculations. Anyway, does not matter if you need to use those numbers, this will remove also the information error.

[macropod]

Hi Catalin,

That won't delete the comment tag (ie "The number in this cell is formatted as text or preceded by an apostrophe.") preceding the number.

[Catalin.B]

Hi Paul,
Maybe i misunderstood the problem.
If the cell contains that message beside the values, obviously the multiply by 1 operation will not change the content. In this case, Find and replace will do the job , of course. I thought that the error message comes from error checking rules , in this case , find and replace will not work, only reformatting the cells will stop those messages, or uncheck that error checking rule in Tools, options-formula-error checking rules.
Maybe achuki will clarify the dilema, i am still not sure which is the case.
I hope i'm wrong
Catalin

[macropod]

Hi Catalin,

Or maybe I misunderstood. If it's just the Excel 'error checking' message, the multiply by 1 operation may indeed resolve the issue. Frankly, if that's all it is and unless the affected 'numbers' need numeric evaluation, I wouldn't even bother. I have one workbook in which every 2nd cell has an 'error checking' message of one kind or another.

As for turning off Excel 'error checking' function, that can certainly improve workbook performance (and delete any distracting messages). Unfortunately, it's also an application-level function that affects all workbooks, not just the one that's active at the time.