Converting text to number when the ToNumber() function isn't ideal

[HenrietteH]

A flat file was parsed into a number of fields, including one field that needs to be converted to a numeric data type.

It looks like this in the data:

000000437
000012587

The resulting number is supposed to have four decimal points, so just applying ToNumber([Field]) won't work because it will convert000000437 to 437 but the required output is 0.0437.

As always, there area couple different ways of going about this:

1. Using the Substring(x, start, length) function, which returns the substring of [x] starting at[start], stopping after [length].This way, the string can be broken into the first 5 characters and the last 4 characters and a decimal point inserted in between the two. Then, the ToNumber() function can be used as usual.

2. Using theToNumber(x, bIgnoreErrors, keepNulls) functions creatively. ToNumber will return 437 for the first row of data and12587 for the second one. However, if the number is then dividedby 10,000,it will return 0.0437 which is the correct result.

3. Using Regular Expression. Regex can recognize a pattern and replace it with another value. In this case, regex can find the last 4 characters of the string: (\d{4})$ and replace them with a decimal point + the pattern it matched to:\.$1

Also take a look at the attached workflow to illustrate the solutions.

[arnulfo]

Great, thanks