String to Numeric Conversion of Exponential Notation

[Silver30]

A formula field needs to convert a character string
containing an exponential value (i.e. "9.99E37&quot to a number.

The ToNumber() function returns the "The string is non-numeric." error.

The val() function truncates and rounds the string up to the "E" (i.e. 10). This is inconsistant with Crystal's documentation which states "This function is designed to work like the Visual Basic function of the same name.". The Visual Basic val() function returns the correct value of 9.99E+37.

Oracle's to_number() function returns the correct value as well.

So, how do I convert a string containing a value in the form listed above (containing the standard exponential 'E' symbol) to the correct number in a formula field within Crystal? (Hopefully, the only answer isn't to dive into and parse the string.)

Thanks.

 

[JoeCrystal]

When I have had to do this I wrote a formula. The formula was similar to:

Dim e as string
Dim es() As String
Dim Mantissa As Number
Dim Power as Number

e = "9.99E37"

Es = Split(e,"E&quot

Mantissa = ToNumber(Es(1))
Power = ToNumber(Es(2))

Formula = Mantissa * (10^Power)

 

[mbarron]

Or if you prefer Crystal Syntax

numbervar base;
numbervar expon;
base:=val({your.field});
expon:=val(mid({your.field},instr({your.field},"E&quot+1) );
base*(10^expon) Mike

 

[JoeCrystal]

Mike:

I hate crystal syntax!

 

[Silver30]

Hi Guys,

I had implemented a routine similar to your suggestions prior to your suggestions. I was actually looking for a single "built-in" function similar to VB's or Oracle's. But, thanks.

However, I thought you might enjoy the response from a Crystal Decisions Product Specialist Team Tech (he should have followed through with the code for 9.9E125):

--Quote--
There is no specific function for Crystal Reports that converts a scientific notation value into an easily readable numeric value.

The following formula outputs the correct numeric value.

NumberVar X := ToNumber(StringAfterChar("9.9E2", "E&quot);
NumberVar Y := ToNumber(StringBeforeChar("9.9E2", "E&quot);

If X = 2 then X := 100
Else if X = 3 then X := 1000;

Y * X

Add further if then else statements if the value after the ‘E’ will be 4, 5 etc.
--End Quote--

Prior to this, he wrote...

--Quote--
The Value 9.9E2, would the numeric value be 198 ? (9.9 * 20 )

If the numeric value for 9.9E2 is not 198, please let us know what the value would be.
--End Quote--

Thanks again.