Conversion Error - is not a number

[elclark]

I have a file that I used a text to column to separate numbers by comma, then I used a data cleanse to get rid of any leading and trailing whitespace, plus tabs, line breaks, and whitespace. However, once I use the Select tool to change the numbers from string to a numerical data type, on some of the numbers I get an error that says it is not a number (Field1:11050 is not a number.) and it deletes the number from the cell. I exported the data before applying the Select and looked at the fields and there aren't any additional spaces, characters, etc so not sure why it's not converting. Is there any way I can keep it from deleting the number even if it doesn't convert? Or any ideas on why it won't convert it from a string to a number.

[dkma]

Hi @elclark 

Try using ToNumber(x) function.

It should work

[T_Willins]

HI @elclark,

You can't rely on simply removing everything but numbers and having what is remaining be able to be converted to a number using a Select tool.  A better method to convert the cleansed field from string to numeric is to use a Multi-Field Formula tool with the field(s) to convert selected, uncheck the Copy Fields and Add box, change the output type to an appropriate numeric type, and use the formula ToNumber([_CurrentField_]).

[elclark]

I tried using the ToNumber function and it still didn't convert, now I'm getting an error that says ToNumber: 11050 lost information in conversion

[T_Willins]

HI @elclark,

What field type are you trying to convert to?

[apathetichell]

Post some example data - @T_Willins instructions should solve your problem - using a MULTI-FIELD FORMULA vs a regular formula is key.... Can you also confirm that any . or , are correct in terms of your localization settings? Also that assumes you aren't bringing in non-integers as integers...

[elclark]

Here are the numbers I'm trying to convert. I did Text to Columns, Split to rows.

[apathetichell]

FYI - I'm seeing one of your numbers has an error (11050) - I think one of the characters isn't a 1 or isn't a 0 - I'm looking into it... It's my only conversion error on a multi-field after datacleansing btw.

My quick research has the first 0 as a tibetan 0...

[T_Willins]

HI @elclark,

Your worksheet has a non-breaking space.  This can happen with downloaded reports.  If you look at cell A232 in your Excel workbook and click on the first 1 in 11050, then backspace, you can see the it takes 2 clicks to move back one whitespace.  You can cleanse this with a RegEx Replace.  See the attached workflow that runs without error.