Formating text to numbers with negative sign to negative numbers, but others do not. Is there a step I need to do before I format to number?

Ask a question
1

I am formatting text to numbers. Some of my values -7600 show up as -7600.00 as expected, some convert to 0. The 0 is incorrect.

How do I correct that issue?

2

Hey @Steven_Darlington - can you share a screenshot of your flow and some of the rows that are converting the negative numbers to 0? What’s your original data source with the numbers formatted as text?

I just did some testing on my end and I wasn’t able to reproduce the issue.

3

image
image2355Ă—710 177 KB

image
image3240Ă—1875 748 KB

image
image2682Ă—1531 298 KB

Here are three screenshots of the show the issue.

I would be willing to share my workflow with you if that would help[…

Steve

4

3 - 3030 Midwest with Vendor name.xlsx

password: 5205

I tried again with this file. I am having the same issue with Row 532. I am sure there are other rows, but I think if I fix this row it will fix them all.

I just do not see the reason it changes to zero.

Steve

5

Hey Steven,

Thanks for sending over your test file. I was able to reproduce the issue, and I think I have a simple workaround for you.

My feeling is this happens when a negative number is stored as text with a comma. To get around this, use a Find and replace step to find all , in the columns you want to target and replace it with a blank value.

find:replace
find:replace894Ă—1462 53.3 KB

Your Format numbers step should now work correctly, and you can even add the comma back in with comma grouping.

1 Like
6

Interesting, good to know. I will do that and let you know. That also explains why some of the credits made it through. Thank you so much.

2 Likes
7

Hey Steven,

Just following up. This bug should be addressed and should no longer format negative integers with commas to 0.

Thanks again for calling this out!

8

Great, thanks you for the update.

I will run it through again.

Steve

9

Hi guys!
I need to know how I can transform text to numbers. Watching Steven’s flow, I think it is with the cleaning data module, but how should I use it to do it?
My data comes from a csv file like the one in this link, that brings me the numbers in text format like this 45,56. It refers to money.
What I finally want to do is to SUM all the operations grouped by MEDIO_PAGO (payment method).

Thanks!

10

I found out how to do it. Just changing “,” for “.”, it recognizes it as numbers.

1 Like