ToNumber Conversion Error

[mberg2512]

Hi,

I am trying to convert field OE Period (pictured below) from a string to a number. 

Using the select tool to convert did not work. 

As a second option I tried to created this formula:  ToNumber([OE Period]) but I am getting a "lost information in conversion" error.

The filter (which requires a number) that comes after the formula seems to be working ok. Is it ok to just ignore these warnings or am I missing something?

[cmcclellan]

Is there NULLs or spaces in some of the rows ?

[CharlieS · Accepted solution]

The only way I found to reproduce your error is to have a field with only a space character in it. An empty or null field will be converted to a 0 with the tonumber function without warning. You'll notice that those warning messages seem to have twice the space they should at the beginning (compared to the last warning line). The trim function will default to eliminating white space values from the ends of your strings.

Try modifying your formula to: tonumber(trim([OE Period))

If that doesn't do it, considering using a RegEx tool to only extract the digits from your field. If your values are only whole numbers with no periods or commas, the expression is just: [\d]+

[mberg2512]

Thank you! The trim function worked perfectly.

[cmcclellan]

@mberg2512 that's where I was heading, but you nailed it a lot better and more succintly :)