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

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?

Datasets

Data Investigation

Accepted answers

Qiu

@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.

bwat2435.yxmd

atcodedog05

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 😀👍

number.yxmd

All comments

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 🙂

cgoodman

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

RemoveSpaces.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703