Converting Scientific Notation

Monarch Data Prep Studio's data extraction engine does not automatically recognize numbers represented in scientific notation format (e.g., 1.2034E+03). Fields with scientific notation are assigned character format.

To convert strings representing scientific notation to numeric expressions, use the Val function in a calculated field expression. The following expression converts the contents of a character field containing scientific notation to a numeric expression:

Val(fieldname)

where fieldname is the name of the field containing a string in the scientific notation format. For example, if the MASS field contained 1.2034E+03, the following function would return the value 1203.4:

Val(MASS)

Removing blank characters

If the character field contains any blank characters (spaces), as in 1.2034 E+03, the Val function will not work properly. You must first remove the blank characters. If the MASS field contains 1.2034 E+03, the following calculated field expression will return the proper result (1203.4):

Val(LSplit(MASS,2," ",1)+LSplit(MASS,2," ",2))

This expression concatenates (+) the right side of the MASS field (E+03) to the left side (1.2034) without the intervening space character, creating an expression (1.2034E+03) which Val can convert properly. This expression uses the LSplit function to split the string into two substrings, neither of which contains the space character.