Convert from Text to Number - Exporting to Excel 2003 from SQL Reporting services 2008 r2

Question

0.00/5 (No votes)

See more:

Hi
I was trying to export a report which is in float format. When I do that, all the numbers in excel have a green small tag beside it saying

"The number in this cell is formatted as text or preceeded by an apostrophe".

I have to sum my report values and this can only be done after I convert each cell to number. Is there any possible way to handle this in report itself ?

Thank You.

Posted 28-Mar-13 17:56pm

Dhivya.J

Updated 23-Aug-18 4:43am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2013-03-28T22:54:00

Solution 1

Here, it has been discussed and shared here: “The number in this cell is formatted as text or preceeded by an apostrophe.”[^]

It says:
To solve this problem we can set:
=Fields!MyField.Value as the Value and set =”###,###,###,##0″ as the Format. You can find the “Format” under the “Appearance” of the properties window.

Posted 28-Mar-13 22:54pm

Sandeep Mewara

Comments

Dhivya.J 2-Apr-13 5:12am

Thank you. But my report is exported to numbers(exactly saying float values), not in # symbol's. My problem is, I want to "manually convert it into number" to sum the whole column.

Member 13959242 · Answer 2 · 2018-08-23T04:44:00

Hello,

The issue may be due to you tried to convert a nonnumeric value to an integral value. For expression, if we are use following expression: =CInt(“ABC”), we would get the #Error value in our report.

The root cause is IIF() function always evaluates both the true part and the false part, even though it returns only one of them. This means both the true part and the false part need to be evaluate.

Please refer to the custom code below to work around the issue:

Public Function Conv(ByVal A as String)
Dim B as string
Dim C as Integer
If isnumeric(A) Then
C=CInt(A)
Return C
else
B=CStr(A)
Return B
End If
End Function

Then, we can use the following expression to calculate the value:
=Code.Conv(Fields!PID.Value)

Regards,
Vinoth Paramasivam