+ Start a Discussion
Dave BerenatoDave Berenato 

Formula to convert Numeric Value to Word of Number

I'm writing a formula to convert a Number Field into a Word.

Basically where 1,234,567 would equal "One Million Two Hundred Thirty Four Thousand Five Hundred Sixty Seven"

But I'm running into the compiled size of the formula error.

Now I've come up with a formula that only works for numbers between 100,000 and 999,999 (which should be fine for about 95% of cases I would be using it), but I'm interested in seeing if anyone has been able to do this in the 5,000 character limit before.
 
CASE(LEFT( RIGHT(TEXT(Offer_Price__c) ,6),1),

"1","One Hundred ",
"2","Two Hundred ",
"3","Three Hundred ",
"4","Four Hundred ",
"5","Five Hundred ",
"6","Six Hundred ",
"7","Seven Hundred ",
"8","Eight Hundred ",
"9","Nine Hundred ",
"10","Ten Hundred ",NULL)

&
IF(
AND(VALUE(LEFT(RIGHT(TEXT(Offer_Price__c),5),2))>9,
VALUE(LEFT(RIGHT(TEXT(Offer_Price__c),5),2))<20),

CASE(LEFT(RIGHT(TEXT(Offer_Price__c),5),2),
"10","Ten Thousand ",
"11","Eleven Thousand ",
"12","Twelve Thousand ",
"13","Thirteen Thousand ",
"14","Fourteen Thousand ",
"15","Fifteen Thousand ",
"16","Sixteen Thousand ",
"17","Seventeen Thousand ",
"18","Eighteen Thousand ",
"19","Nineteen Thousand ",NULL),


CASE(LEFT(RIGHT(TEXT(Offer_Price__c),5),1),

"2","Twenty ",
"3","Thirty ",
"4","Forty ",
"5","Fifty ",
"6","Sixty ",
"7","Seventy ",
"8","Eighty ",
"9","Ninety ",NULL)

&

CASE(LEFT(RIGHT(TEXT(Offer_Price__c),4),1),
"0","Thousand",
"1","One Thousand ",
"2","Two Thousand",
"3","Three Thousand ",
"4","Four Thousand ",
"5","Five Thousand ",
"6","Six Thousand ",
"7","Seven Thousand ",
"8","Eight Thousand ",
"9","Nine Thousand ",NULL))

 
Best Answer chosen by Dave Berenato
Narender Singh(Nads)Narender Singh(Nads)

Hi Dave,
Writing this kind of formula on formula editor is bound to hit limits. If you want to stick to formula then you can set a Upper value for the numeric value using a validation rule.
Otherwise I suggest you write an apex logic for this.

You can refer this link for writing the apex logic: http://salesforcewithkiran.blogspot.in/2013/05/number-to-words-in-apex.html

Thanks!

All Answers

Narender Singh(Nads)Narender Singh(Nads)

Hi Dave,
Writing this kind of formula on formula editor is bound to hit limits. If you want to stick to formula then you can set a Upper value for the numeric value using a validation rule.
Otherwise I suggest you write an apex logic for this.

You can refer this link for writing the apex logic: http://salesforcewithkiran.blogspot.in/2013/05/number-to-words-in-apex.html

Thanks!

This was selected as the best answer
Dave BerenatoDave Berenato
Hi Narender,

Thanks for the reference. Would this be something that had to be written in a sandbox and tested?

I ended up using a workaround where I have formula fields for each 3 digit section and then a workflow rule updates another field by putting them together, since I'm pretty much only dealing with 6 digit numbers.
Narender Singh(Nads)Narender Singh(Nads)
Hi Dave,

Yes you are correct. You will have to write a test class for your apex logic with a code coverage above 75%. And then you will have to push it to your production.