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.
(c) 2023 Altair Engineering Inc. All Rights Reserved.