ToText formula output broken for some numbers

I was importing some data that seemed to require me to use the Decimal number field to avoid having issues importing it. I was making use of that number to create a list of ids, so I needed it as text and ran into issues. Some of the number worked fine, but others came out of the function looking like they were unable to be converted to text. It seems like the length of the number had something to do with it.

The field I’m converting to text is a number field configured to have a single decimal point. When I pass that through ToText, I get the following.

Correct: ToText(256292011) = 256292011.0
Incorrect: ToText(21393064011) = ##########.##########

Wanted to bump this one. Is there a workaround for this? I tried different formulas to avoid the issue, but it seems the only option would be to manually copy the decimal value into an integer.

Bumping this issue again. Rounding the number didn’t help and there is no formula to convert a decimal number to an integer.

Have you tried created a new number formula field which merely points to the decimal field, but with the number of decimal places set to zero

and then use this field in your totext() wherever you need it.

It’s hacky, but has worked for me in the past.

Thanks for looking into this. At first I was thinking I overlooked doing this, but when looking at the schema again I seem to have tried it. I have this long-ish id as a decimal, then have tried converting it to an int and to text via a formula. The integer ends up being empty and the text formula ends up with the same issue with the XXX’s.


@Polina_Zenevich contacted me through chat about the error, since I guess it drove lots of error messages or something. However, the suggestion was that there is a way to convert decimal to integer, but I’m not able to find a way to do that. I understand resources are strained right now, so I will try to find another workaround.

We are looking into the ToText weirdness.

Meanwhile: how do you use the original Decimal Field?
If it’s just a temporary “storage”, you could migrate the data using copy-paste on a Table View instead of a Text Formula Field.

1 Like

Here’s an ugly hack:

Replace(Left(ToText((Decimal + 0.5) / 1000),Length(ToText((Decimal + 0.5) / 1000)) - 1),".","")

It works as long as the original Decimal number is an integer.
It will allow you to convert numbers up to 9,999,999,999,999
A side-effect is that any numbers less than 1000 will get leading zeroes.

If there are limitations on the possible values of Decimal (e.g. always a certain length) then there would be some simplifications possible.


@rothnic i am sorry for inconvenience :pray:

It is a bug with the number of decimal positions before the dot.
By default 10 decimal positions before the dot were supported and 21393064011 having 11 decimal positions before the dot fails to convert to text.
Fix allowing 15 decimal positions before the dot will be deployed this week.

P.S. i believe that @Chr1sG guides some hacker special forces because of producing unbelievable workarounds!

1 Like

Nice, I tried a regex replace at one point, but I didn’t think about pushing more of the data into the decimal end of the number. This seems to have fixed the issue for me temporarily. Thanks a bunch!