String 5E11 in Excel is treated as a numeric

[sun21170]

"5E11" in excel displays as 500000000000 or 5e11 which is the numeric scientific notation. How can I tell Excel to display 5E11 as a string "5E11"?

Thanks
Sunil

 

[Aladin Akyurek]

"5E11" in excel displays as 500000000000 or 5e11 which is the numeric scientific notation. How can I tell Excel to display 5E11 as a string "5E11"?

Thanks
Sunil

Enter such in a text-formatted cell or try to type it as '5E11.

 

[anandvarma]

try this

a1&""

 

[sun21170]

I found the solution. I made sure that I put the following value in the cell:
="5E11"
The double quotes should be there and part of the string. Then Excel will automatically remove the = sign and also the leading and trailing double quotes, and treat 5E11 as a string and not a numeric. This solved my problem.

Preceding 5E11 by a single quote works but then Excel includes the single quote as part of the value, so Excel thinks the value is '5E11 rather than just 5E11. So I think the single quote approach may not be an appropriate solution here.

Thanks
Sunil

 

[dk]

I found the solution. I made sure that I put the following value in the cell:
="5E11"
The double quotes should be there and part of the string. Then Excel will automatically remove the = sign and also the leading and trailing double quotes, and treat 5E11 as a string and not a numeric. This solved my problem.

Preceding 5E11 by a single quote works but then Excel includes the single quote as part of the value, so Excel thinks the value is '5E11 rather than just 5E11. So I think the single quote approach may not be an appropriate solution here.

Thanks
Sunil

I'm not sure how you're retrieving your value but Excel will ignore the apostrophe if you're using VBA and something like this:

Sub Testing()
Dim sSomeValue As String

sSomeValue = "'5E11"

Worksheets.Add

Range("A1").Value = sSomeValue

MsgBox Range("A1").Value

End Sub

DK

 

[Aladin Akyurek]

...Preceding 5E11 by a single quote works but then Excel includes the single quote as part of the value, so Excel thinks the value is '5E11 rather than just 5E11. So I think the single quote approach may not be an appropriate solution here.

Thanks
Sunil

In A1 enter:

'5E11

In B1 enter:

=LEN(A1)

Are you getting 5?

 

[sun21170]

Actually, I tested the single quote and also the double quote approach, and found the extra characters of single quote and double quote show up when the cell is in edit mode. But when cell is not in edit mode then the single quote or double quote do not show up. Please look at this video to see what I am saying : http://screencast.com/t/AVNYkLuT
So it seems those extra characters are not ignored completely by Excel.
You know why in edit mode these extra characters show up?

Thanks
Sunil

 

[Aladin Akyurek]

Actually, I tested the single quote and also the double quote approach, and found the extra characters of single quote and double quote show up when the cell is in edit mode. But when cell is not in edit mode then the single quote or double quote do not show up. Please look at this video to see what I am saying : http://screencast.com/t/r4aq0ur6
So it seems those extra characters are not ignored completely by Excel.
You know why in edit mode these extra characters show up?

Thanks
Sunil

The way the single quote works is just by design I guess. And useful when it appears in Edit mode. Othwerwise, you would be wondering why 5E11 is not seen as number.

Just typing

"5E11"

in a cell would make double quotes part of the entry.

="5E11"

is a different matter. The equal sign forces an evaluation, making Excel to create a text string with the cell itself transformed into a text formatted cell.

'5E11

does not change the format of the cell itself. If it was General before, it stays that way after such an entry.

 

[sun21170]

I guess Excel acts strange sometimes.
So do you suggest just using a single quote at start of such a string, when exporting ? I am exporting data to a tab delimited file using C# code.

Thanks
Sunil

 

[Aladin Akyurek]

I guess Excel acts strange sometimes.
So do you suggest just using a single quote at start of such a string, when exporting ? I am exporting data to a tab delimited file using C# code.

Thanks
Sunil

I think that would work. You could also try CSV or TXT formats.