SSRS Issue with values that are text and number exporting into excel?

Question

I am having an issue where I have similar hyphenated values 1304-4(text) and 1306 (number) The main issue I am trying to overcome, is when I export my SSRS report (.rdl) to excel, I get an error in excel, where it shows numbers are formatted as text. I seek to avoid this, and am open to any solutions to do so. I have searched vigorously and not found a solution that works. I have noticed that I can multiply the numerical values 1306*1.0000 and that would fix the issue, if not for the text values 1304-4*1.000 = null in report. Is there any way to accomplish this? I can cast my dataset as any other type of values, however SSRS only wants one kind of value in a field, as far as I am aware. Any thoughts or help would be appreciated.

Answer 1

If the 1304-4 and 1306 values occur in the same column, why would you not treat them all as text? If it's a case where the column values should be treated as text in some cases and as numbers in others, that's frankly a horrible database design.