SPELLNUMBER

=SPELLNUMBER(range)
range
range of cells with numbers to be spelled out
SPELLNUMBER takes a number and converts it to spelled out words
lrobbo314

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,929
Office Version
  1. 365
Platform
  1. Windows
SPELLNUMBER takes a number and converts it to spelled out words. There are a number of helper Lambdas which are also listed.

SPELLNUMBER
Excel Formula: 
=LAMBDA(range,
    MAP(range,
        LAMBDA(n,
            LET(
                buckets,SPLITNUM(ZPAD(n)),
                r,ROWS(buckets),
                t,MAP(buckets,LAMBDA(b,HUNDRED(ARRAYFROMTEXT(b)))),
                z,INDEX(HUGE(),SEQUENCE(r,,r,-1)),
                    TEXTJOIN(" ",,t&" "&z)
            )
        )
    )
)

SML
Excel Formula: 
=LAMBDA(LET(sml,{"";"one";"two";"three";"four";"five";"six";"seven";"eight";"nine";"ten";"eleven";"twelve";"thirteen";"fourteen";"fifteen";"sixteen";"seventeen";"eighteen";"nineteen"},sml))

TENS
Excel Formula: 
=LAMBDA(LET(tens,{"";"twenty";"thirty";"forty";"fifty";"sixty";"seventy";"eighty";"ninety"},tens))

HUGE
Excel Formula: 
=LAMBDA(LET(huge,{"";"thousand";"million";"billion";"trillion";"quadrillion";"quintillion";"sextillion";"septillion";"octillion";"nonillion";"decillion"},huge))

HOR
Excel Formula: 
=LAMBDA(input,IF(input="0","",INDEX(SML(),input+1) & " hundred"))

ZPAD
Excel Formula: 
=LAMBDA(text,REPT("0",MOD(3-MOD(LEN(text),3),3))&text)

TEXTREVERSE
Excel Formula: 
=LAMBDA(text,LET(cnt,LEN(text),TEXTJOIN(,,MID(text,SEQUENCE(cnt,,cnt,-1),1))))

HYPHENATE
Excel Formula: 
=LAMBDA(text,chunk_size,
    MAP(text,
        LAMBDA(t,
            TEXTJOIN("-",,MID(t,SEQUENCE(ROUNDUP(LEN(t)/chunk_size,0),,,chunk_size),chunk_size))
        )
    )
)

Spell Numbers
AB
1NumberSpell
24four
312twelve
4314three hundred fourteen
52,145two thousand one hundred forty five
678,542seventy eight thousand five hundred forty two
7652,145six hundred fifty two thousand one hundred forty five
84,521,456four million five hundred twenty one thousand four hundred fifty six
985,452,145eighty five million four hundred fifty two thousand one hundred forty five
10362,514,789three hundred sixty two million five hundred fourteen thousand seven hundred eighty nine
Sheet6
Cell Formulas
RangeFormula
B2:B10B2=SPELLNUMBER(A2:A10)
Dynamic array formulas.
 
Upvote 0
Adding to this conversation:
While this is not as elegant as Xlambda's version, it has two advantages.
1) it is all self contained (i.e. one function with no need for ancillary functions),
2) it includes cents (at least for smaller values).
This is oriented to reflect the written out numbers as dollars and cents.

Excel Formula: 
=LAMBDA(Array,
LET(N2Txt,LAMBDA(Val,
   LET(I,SEQUENCE(5),
           I_2,SEQUENCE(6),
           ValText,REPT("0",15-LEN(INT(Val)))&INT(Val),
           Decim,ROUND(Val-INT(Val),2)*100,
           ValAr,VALUE(MID(ValText,SEQUENCE(5,,,3),3)),
           SumCheck,SUM(ValAr),
           VSAr,VSTACK(ValAr,Decim),
           TxtAr,LET(Hundreds,IF(VSAr>=100,CHOOSE(LEFT(VSAr,1),"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),""),
                               Val_10,VALUE(RIGHT(VSAr,2)), Tens, IFS(Val_10=0,0,Val_10<20,CHOOSE(Val_10,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen") , TRUE,CHOOSE(LEFT(Val_10,1)-1,"Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(RIGHT(Val_10,1)="0","","-" &CHOOSE(RIGHT(Val_10,1),"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"))),IFS(VSAr=0,"Zero",Hundreds="",Tens,Tens<>0,Hundreds&" Hundred "&Tens,TRUE,Hundreds&" Hundred")
            ),
           TEXTJOIN(" ",TRUE,IFS(AND(SumCheck=0,Decim=0),"Zero Dollars and Zero Cents", SumCheck=0,"Zero Dollars", INDEX(TxtAr,I_2)="Zero","",TRUE,INDEX(TxtAr,I_2)&CHOOSE(I_2," Trillion"," Billion"," Million"," Thousand",IF(Decim=0," Dollars"," Dollars and")," Cents")))
     )),MAP(Array,N2Txt))
)

Sample Data
5565754956813.42​
Five Trillion Five Hundred Sixty-Five Billion Seven Hundred Fifty-Four Million Nine Hundred Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
565754956813.42​
Five Hundred Sixty-Five Billion Seven Hundred Fifty-Four Million Nine Hundred Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
65754956813.42​
Sixty-Five Billion Seven Hundred Fifty-Four Million Nine Hundred Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
5754956813.42​
Five Billion Seven Hundred Fifty-Four Million Nine Hundred Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
754956813.42​
Seven Hundred Fifty-Four Million Nine Hundred Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
54956813.42​
Fifty-Four Million Nine Hundred Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
4956813.42​
Four Million Nine Hundred Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
956813.42​
Nine Hundred Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
56813.42​
Fifty-Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
6813.42​
Six Thousand Eight Hundred Thirteen Dollars and Forty-Two Cents
813.42​
Eight Hundred Thirteen Dollars and Forty-Two Cents
13.42​
Thirteen Dollars and Forty-Two Cents
3.42​
Three Dollars and Forty-Two Cents
0.00​
Zero Dollars and Zero Cents
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top