Formula for Number to Text


#1

Hi all,

I would like to create a formula field (or any other way) that takes a number field and “translates” it to a text.

ex: 1053 --> One thousand and fifty three.

any idea on how to achieve this?


#2

There’s not an easy way to do this. I’m sure it’s possible with a gigantic nested conditional formula, but that’s going to be a pain to write. If you’re interested in trying, you’ll probably be making use of:

SUBSTITUTE({Field with Number}, "1", "one ")  <-- For each digit 1-9

I would have 10 different fields for this, each one taking the output of the one before it and converting its assigned digit to the word representing that digit.

Next, you’d probably want to figure out the base10 thousands/hundreds language needed with conditional logic, and apply those words in the right place, with statements something like this:

IF({Field with Number} > 999, REPLACE({Output of last digit replace field}, FIND(" ",{Output of last digit replace field}),1," thousand "))

You’ll be left with 0's still in there, so you’ll need conditional statements to figure out where the 0's are in relation to other digits in the original number to determine the base10 tens to convert (if five is followed by a 0, and the 0 is in the last place, convert the five and 0 to fifty).

It’s not going to be easy – and I’d suggest taking it one field doing one conversion job at a time before trying to combine it all into a monolithic formula.