Identify records with ToNumber Conversion Errors

[JeremyN]

Hi,

I would like to have a check for non-numeric values in a numeric field.  I would like this to apply to decimals, values with commas, and scientific notation (E in the field).  As far as I can tell the ToNumber formula would list any of the errors I am looking to identify as conversion errors.  Is there a way to add information from conversion errors log to the data itself?  I've tried other techniques such as a data cleanse tool to remove characters from a field and comparing to the original field, but this doesn't work for scientific notation.  

Thanks!

[DavidP]

Just make sure when using ToNumber that you include the optional parameters. By default conversion errors are converted to zero. 

I don't know that there is a way to include conversion errors in the data, but you can at least force those fields to be null.

[JeremyN · Accepted solution]

The best solution I've found is using a data cleanse tool to remove characters from a field, comparing to the original field to look for differences, and checking for a value of 'E' indicating scientific notation in the original field.  Formula would look like the following.

([Value] != [Value W Alpha Removed]) and NOT Contains(Uppercase([Value]), "E")

This will identify values such as 100k.