Str converts to scientific notation

From the LR:

"For numbers below 0.00001, Str automatically switches to scientific notation and you then get 7 significant digits. "

Is there a way to override this behavior, so that Str simply truncates decimals 6 and 7, instead of returning the string in scientific notation.

For example, I need Str(0.000012), to return 0.00001.

Use format? Format(d, “0.#####”).

Of Str(d,“0.#####”) if you don’t want to respect international settings.

1 Like

Thanks for the answers.

Str(d, “0.#####”) was what I was looking for.

The output of

Str(1.0, "-0.#######")

is “1.”

Is there a way to drop the decimal point at the end, if there are no digits after the decimal point (e.g. output is “1” instead of “1.”)?

dim temp as string = Str(1.0, “-0.#######”)
if right ( temp, 1 ) = “.” then temp = left(temp, len(temp) -1 )

Thanks Norman.

I was hoping there is a way to avoid an if statement or doing string manipulation, by changing the format string. I’m using this instruction in an loop that iterates 1 million times, so I need to limit the amount of instructions used in the loop.

Hmmm, I just remembered Michael Diehr recommended a fast check for integers in the “Is whole number” conversation…

dim temp as string

// dval = some double value
  
if (dVal \\ 1) = dVal then // whole number?
    msgbox Str(dVal, "-0")
else
    msgbox Str(dVal, "-0.#######")
end if

This is the method I finally settled on to convert a double to string, while preventing automatic scientific notation, and dropping the decimal symbol if it is a whole number:

Private Function X3_Str(d As Double) As String
  if (d \\ 1) = d then // whole number?
    return Str(d, "-0")
  else
    return Str(d, "-0.#######")
  end if
End Function

For some reason i printed the 67 and it shows up on the printout as 6.7e+1
but in other spots on the exact same page the 67 us printed as 67.

I guess I just need to use formatting no matter what to guarantee it won’t e notation out.

However, my sample just now goes against the assertion that this only happens for small numbers.

Here is a method that I use for detecting integer values:

Public Function IsInteger(d As Double) as Boolean
  //returns true if the argument is an integer value to 10 or more decimal places
  return d-floor(abs(d+5e-11))<1e-10
End Function

This seemed to fix my problem. I will keep an eye on it…these e+1 notations seem to crop up after I think “I fixed them all”.

…and 3 more years later. I have a specific need for an oddly formatted Excel sheet I have to read in. Alwyn, in the short function, you have:

if (d \\ 1) = d then // whole number?

Is that supposed to be integer division with just one slash like this? :

if (d \ 1) = d then

The code throws an error with \ used. Seems to work as I would expect with the normal integer division. Or am I missing something that \ is supposed to do? Maybe it didn’t get translated correctly in the move to the new forum?

Thanks in advance.

Hi Merv,

Yes, the \\ is supposed to be a single slash …

if (d \ 1) = d then

I’m not sure if Excel supports the nice \ shorthand for integer division, you might have to use the Floor function?

Something like…

if Floor(d / 1) = d then

You may read the documentation… and also the page bottom for more information…

In Str(), you would have discovered Format, Ceil, Floor, etc. (in the See Also paragraph).

Thanks Alwyn. It is in Xojo code, the reader is actually LibXL in the Monkeybread libraries, which reads in the cell as a number or a string and then I have to parse it. This particular Excel file has really big integer values that were getting translated as scientific notation. The function you posted above actually works really well, I’ve been testing it today. Thanks again, so nice to be able to get help here when needed. And thanks for the response Emile, I haven’t been on the forums much lately too busy with work, but checking in occasionally.

Great stuff… glad to hear it helped :wink: