Number stored as string converts to 0 when converted to double using ToNumber or Select

[bwat2435]

Hi All,

I have a column of numbers that are stored as  V_String. I need to do some arithmetic on them so tried converting to Double using both the Select tool and ToNumber formula. Both times, all of the "numbers" were converted to 0. This has never happened to me before so I'm not sure what to do.

Any ideas please?

[Qiu]

@bwat2435 
Can you give us some sample of the strings, or the workflow even better.

[atcodedog05]

Hi @bwat2435 

As @Qiu  mentioned if you give us the scenario where you are facing the issue. We can troubleshoot it for you 🙂

[bwat2435]

Thank you both!

See attached a subset of the data. Column A is what I parsed, and columns C and D are the two columns that have data that needs to be converted to a Double. Note - this works for column D but not for column C.

Thanks in advance!

[Qiu · Accepted solution]

@bwat2435 
I think there is leading space in your C column and with Cleansing Tool we can fix it.
But curiously, Alteryx usually gives warning for this.

[atcodedog05 · Accepted solution]

Hi @bwat2435 

There is leading space before the number. Hence it was creating issue. 

By using data cleansing tool you can remove it and convert. I have a dummy calculation C+D

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Qiu]

@atcodedog05 
You need to give my thread a like aso,  buddy.😁

[cgoodman3]

edit: just seen @Qiu and @atcodedog05 have given you replies, but just in case others come across this thread as well as the space below are other common conversion errors.

Do your numbers have things like commas like this 1,425 or currency symbols like $244.58? If so then you’ll need to remove these using a formula tool with the replace function first.

[RolandSchubert]

Hi @bwat2435 ,

another option would be to use a Multi-Field Formula tool to remove spaces, I think it's the more flexible as you can customize is to your specific needs. The attached sample removes spaces only, but can easily be extended to other characters.

Best,

Roland