• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unexpected Scientific Notation conversion

polarisking

polarisking

Member
I'm having trouble with VBA converting what should be a text value into a Scientific Notation value.

For Value = 613609A60, the formula Format(Value,"000000000") returns
613609A60, as we'd expect.

For Value = 613609E60, the formula Format(Value,"000000000")
613609000000000000000000000000000000000000000000000000000000000000, as we'd expect the all numeric save the 7th position = E denoting Scientific Notation.

However . . .
For Value = 613609D60, the formula Format(Value,"000000000") returns
613609000000000000000000000000000000000000000000000000000000000000
NOT what we'd expect (at least me).

Am I missing something obvious? I'd expect 613609D60 to come back as 613609D60.

Why am I doing this? US Cusips can begin with leading zeros and this little piece of code insures (so I thought :) ), I'd end up with a length of 9 each time. The source file's value format is general and that's why I'm having to compensate.

Thank you, in advance, for any assistance.
 
For Value = 613609D60...
Click to expand...

In VBE, try typing in 613609D60 without quotes. You'll see how it's converted into scientific number. By using Format function on the value, you are basically coercing text that can be interpreted as numeric into numeric.
 
Thank you both. Here's the VBE display answering the question as to quotes, or not

? format("613609D60","000000000")
613609000000000000000000000000000000000000000000000000000000000000
? format(613609D60,"000000000")
613609000000000000000000000000000000000000000000000000000000000000

Quotes appear not to matter. The issue is the letter D and how it's being interpreted by the VBA function FUNCTION. For a Value of A, with no quotes, we get an error message of

? format(613609A60,"000000000")
Compile error: Expected: list separator or )

Quotes work
? format("613609A60","000000000")
613609A60

Curious . . .
 
You misunderstand me.

In VBE type in following. Then hit enter. See what change is applied.
Code: 
x = 613609D60

You should see above interpreted as numeric.

Where as 613609A60 has no numeric equivalent, and the error message is indicating that you need to nest it in double quotes to signify it as string.
 
Chihiro, why would it be correct to interpret 613609D60 as numeric? I fully understand if the letter were E rather than D that Excel would believe the value is SciNot, but I've never seen any letter other than E resolve back into a numeric value.
 
You should get:
upload_2018-8-7_22-25-5.png

So I must ask

What version of Excel are you using ?
What Language does your PC use?
 
Hui, could you start a conversation with me (I can't with you) on a sensitive matter please?
 
You must log in or register to reply here.
Back
Top