Identify records with ToNumber Conversion Errors

Question

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!

JeremyN · Accepted Answer

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.