SPELLNUMBER

[lrobbo314]

SPELLNUMBER takes a number and converts it to spelled out words. There are a number of helper Lambdas which are also listed.

SPELLNUMBER

=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

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

TENS

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

HUGE

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

HOR

=LAMBDA(input,IF(input="0","",INDEX(SML(),input+1) & " hundred"))

ZPAD

=LAMBDA(text,REPT("0",MOD(3-MOD(LEN(text),3),3))&text)

TEXTREVERSE

=LAMBDA(text,LET(cnt,LEN(text),TEXTJOIN(,,MID(text,SEQUENCE(cnt,,cnt,-1),1))))

HYPHENATE

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

Spell Numbers
	A	B
1	Number	Spell
2	4	four
3	12	twelve
4	314	three hundred fourteen
5	2,145	two thousand one hundred forty five
6	78,542	seventy eight thousand five hundred forty two
7	652,145	six hundred fifty two thousand one hundred forty five
8	4,521,456	four million five hundred twenty one thousand four hundred fifty six
9	85,452,145	eighty five million four hundred fifty two thousand one hundred forty five
10	362,514,789	three hundred sixty two million five hundred fourteen thousand seven hundred eighty nine
Sheet6

Cell Formulas
Range	Formula
B2:B10	B2	=SPELLNUMBER(A2:A10)
Dynamic array formulas.

 

[steven88]

 

[lrobbo314]

Forgot this one is needed as well.

ARRAYFROMTEXT

=LAMBDA(text,[to_rows],LET(a,text,n,LEN(a),r,MID(a,SEQUENCE(n),1),IF(ISOMITTED(to_rows),r,IF(to_rows,r,TRANSPOSE(r)))))

 

[steven88]

i think i need SPLITNUM

 

[lrobbo314]

I forgot to add SPLITNUM and HUNDRED.

Thanks for pointing this out.

SPLITNUM

=LAMBDA(text,
    TEXTSPLIT(TEXTREVERSE(INDEX(HYPHENATE(TEXTREVERSE(text),3),1)),,"-")
)

HUNDRED

=LAMBDA(text,
    LET(
        r,text,
        a,INDEX(r,1),
        b,INDEX(r,2),
        c,INDEX(r+1,3),
        x,IF(INT(b)<2,
            INDEX(SML(),b&c),
            INDEX(TENS(),b) & " " & INDEX(SML(),c)
            ),
        TRIM(HOR(a) &" " & x)
    )
)

 

[Xlambda]

Hi, I think your function returns an error for numbers ending, starting with, or containing "19". Instead of debugging it, it was simpler for me to write a new function from scratch.
Can you check?
SPELLNR(ar) calls SD([k]) Spell Digits
ar: array of numbers (can be in text format for large numbers (keeps precision) or numbers in general or number format)

=LAMBDA(ar,
    LET(
        f, LAMBDA(n,
            LET(
                t, REPT(0, 36 - LEN(n)) & n,
                m, --MID(t, SEQUENCE(36), 1),
                w, WRAPROWS(m, 3),
                a, TAKE(w, , 1),
                b, INDEX(w, , 2),
                c, TAKE(w, , -1),
                x, INDEX(SD(), a + 1),
                y, INDEX(SD(1), b),
                z, INDEX(SD(), IF(b = 1, c + 10, c) + 1),
                g, TRIM(x & " " & IF(x = "", "", SD(3)) & " " & y & " " & z),
                TRIM(TEXTJOIN(" ", , IF(g = "", "", g & " " & SD(2))))
            )
        ),
        MAP(ar, f)
    )
)

SD([k]) Spell Digits, contains all the names in one place, compact form, easy to change if we need spelling in other languages,
k= omitted/0,1,2,3

=LAMBDA([k],SWITCH(k,1,{"";"twenty";"thirty";"forty";"fifty";"sixty";"seventy";"eighty";"ninety"},2,{"decillion";"nonillion";"octillion";"septillion";"sextillion";"quintillion";"quadrillion";"trillion";"billion";"million";"thousand";""},3,"hundred",{"";"one";"two";"three";"four";"five";"six";"seven";"eight";"nine";"ten";"eleven";"twelve";"thirteen";"fourteen";"fifteen";"sixteen";"seventeen";"eighteen";"nineteen"}))

this is what SD returns for different k values

Book2.xlsx
	A	B	C	D	E	F	G	H	I
1		k argument values
2
3		=SD()		=SD(1)		=SD(2)		=SD(3)
4						decillion		hundred
5		one		twenty		nonillion
6		two		thirty		octillion
7		three		forty		septillion
8		four		fifty		sextillion
9		five		sixty		quintillion
10		six		seventy		quadrillion
11		seven		eighty		trillion
12		eight		ninety		billion
13		nine				million
14		ten				thousand
15		eleven
16		twelve
17		thirteen		Note: Empty strings at the beginning or at the end of vectors
18		fourteen		have a deliberate functionality purpose 😉
19		fifteen
20		sixteen
21		seventeen
22		eighteen
23		nineteen
24
Sheet9

Cell Formulas
Range	Formula
B3,H3,F3,D3	B3	=FORMULATEXT(B4)
B4:B23	B4	=SD()
D4:D12	D4	=SD(1)
F4:F15	F4	=SD(2)
H4	H4	=SD(3)
Dynamic array formulas.

The errors:

Book2.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3						=SPELLNUMBER(B4:D8)
4		19	19545	119010		#REF!	#REF!	#REF!
5		219	19592	119042		#REF!	#REF!	#REF!
6		2319	19594	219229		#REF!	#REF!	#REF!
7		23419	19564	219230		#REF!	#REF!	#REF!
8		1194	31932	45191		one thousand one hundred ninety four	thirty one thousand nine hundred thirty two	forty five thousand one hundred ninety one
9
10
11						=SPELLNR(B4:D8)
12						nineteen	nineteen thousand five hundred forty five	one hundred nineteen thousand ten
13						two hundred nineteen	nineteen thousand five hundred ninety two	one hundred nineteen thousand forty two
14						two thousand three hundred nineteen	nineteen thousand five hundred ninety four	two hundred nineteen thousand two hundred twenty nine
15						twenty three thousand four hundred nineteen	nineteen thousand five hundred sixty four	two hundred nineteen thousand two hundred thirty
16						one thousand one hundred ninety four	thirty one thousand nine hundred thirty two	forty five thousand one hundred ninety one
17
Sheet1

Cell Formulas
Range	Formula
F3,F11	F3	=FORMULATEXT(F4)
F4:H8	F4	=SPELLNUMBER(B4:D8)
F12:H16	F12	=SPELLNR(B4:D8)
Dynamic array formulas.

 

[lrobbo314]

I just went through and saw how your version works. Very cool! I especially like the use of WRAPROWS. Much more elegant than how I was doing it.

I am not sure why you were having the issues with the number 19. Seems to be working on my end.

Insert Hyphens Spell Words II.xlsm
	A	B
1	Number	Spell
2	19	nineteen
3	219	two hundred nineteen
4	1,984	one thousand nine hundred eighty four
5	19,469	nineteen thousand four hundred sixty nine
Sheet6

Cell Formulas
Range	Formula
B2:B10	B2	=SPELLNUMBER(A2:A10)
Dynamic array formulas.

 

[Xlambda]

Thanks!
New workbook, 11 functions copied one by one from this thread. Error still there.

Book1
	A	B	C	D
1
2			=SPELLNUMBER(B3)
3		219	#REF!
4		19	#REF!
5		1984	one thousand nine hundred eighty four
6		19469	#REF!
7
Sheet1

Cell Formulas
Range	Formula
C2	C2	=FORMULATEXT(C3)
C3:C6	C3	=SPELLNUMBER(B3)

 

[lrobbo314]

I don't know. Weird.

 

[Xlambda]

Uf...There is no "weird" in Excel, everything has an explanation.
Quite simply, the problem is with your HUNDRED function. You can correct it now.

=LAMBDA(text,
    LET(
        r,text,
        a,INDEX(r,1),
        b,INDEX(r,2),
        c,INDEX(r+1,3),
        x,IF(INT(b)<2,
            INDEX(SML(),b&c),
            INDEX(TENS(),b) & " " & INDEX(SML(),c)
            ),
        TRIM(HOR(a) &" " & x)
    )
)

spellnumber error.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1
2		1	1	8
3
4		=LAMBDA(text, LET( r,text, a,INDEX(r,1), b,INDEX(r,2), c,INDEX(r+1,3), x,IF(INT(b)<2, INDEX(SML(),b&c), INDEX(TENS(),b) & " " & INDEX(SML(),c) ), TRIM(HOR(a) &" " & x) ) )(B2:D2)
5		one hundred eighteen
6
7		variables: b=1 , c=9, b&c=19 => x=INDEX(SML(),19) = eighteen => numbers ending in 18 or <=18 are ok
8
9
10
11		1	1	9
12
13		=LAMBDA(text, LET( r,text, a,INDEX(r,1), b,INDEX(r,2), c,INDEX(r+1,3), x,IF(INT(b)<2, INDEX(SML(),b&c), INDEX(TENS(),b) & " " & INDEX(SML(),c) ), TRIM(HOR(a) &" " & x) ) )(B11:D11)
14		#REF!
15
16		variables: b=1 , c=10, b&c=110 => x=INDEX(SML(),110) => #REF error												b&c should deliver 20 instead of 110
17
Sheet2

Cell Formulas
Range	Formula
B4,B13	B4	=FORMULATEXT(B5)
B5,B14	B5	=LAMBDA(text, LET( r,text, a,INDEX(r,1), b,INDEX(r,2), c,INDEX(r+1,3), x,IF(INT(b)<2, INDEX(SML(),b&c), INDEX(TENS(),b) & " " & INDEX(SML(),c) ), TRIM(HOR(a) &" " & x) ) )(B2:D2)