Numbers to Words without VBA

S

sukming

New Member
Joined
May 23, 2024
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
I saw Peter Menhennet formula at Leila's youtube (Convert NUMBERS to WORDS in Excel (No VBA))
Need help if anyone knows the formula for Malaysia's format?
the cents want it in words
example : RM 1,234,567.89 ---> One Million Two Hundred Thirty Four Thousand Five Hundred Sixty Seven & Cents Eighty Nine Only.
cant seems to get it


This is the formula (B3 is the selected cell) :-
=CHOOSE(LEFT(TEXT(B3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

&IF(--LEFT(TEXT(B3,"000000000.00"))=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),2,1)=0,--MID(TEXT(B3,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(B3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")

&IF(--MID(TEXT(B3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(B3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))

&IF((--LEFT(TEXT(B3,"000000000.00"))+MID(TEXT(B3,"000000000.00"),2,1)+MID(TEXT(B3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(B3,"000000000.00"),4,1)+MID(TEXT(B3,"000000000.00"),5,1)+MID(TEXT(B3,"000000000.00"),6,1)+MID(TEXT(B3,"000000000.00"),7,1))=0,(--MID(TEXT(B3,"000000000.00"),8,1)+RIGHT(TEXT(B3,"000000000.00")))>0)," Million and "," Million "))

&CHOOSE(MID(TEXT(B3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

&IF(--MID(TEXT(B3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),5,1)=0,--MID(TEXT(B3,"000000000.00"),6,1)=0)," Hundred"," Hundred"))

&CHOOSE(MID(TEXT(B3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")

&IF(--MID(TEXT(B3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(B3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))

&IF((--MID(TEXT(B3,"000000000.00"),4,1)+MID(TEXT(B3,"000000000.00"),5,1)+MID(TEXT(B3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(B3,"000000000.00"),7,1)+MID(TEXT(B3,"000000000.00"),8,1)+MID(TEXT(B3,"000000000.00"),9,1))=0,--MID(TEXT(B3,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))

&CHOOSE(MID(TEXT(B3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

&IF(--MID(TEXT(B3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),8,1)=0,--MID(TEXT(B3,"000000000.00"),9,1)=0)," Hundred "," Hundred "))

&CHOOSE(MID(TEXT(B3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")

&IF(--MID(TEXT(B3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))

&" & "&"Cents " &RIGHT(TEXT(B3,"000000000.00"),2)&" Only."
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
How about a different solution?

Book1
ABC
1951,458,769,231.13Nine Hundred Fifty One Billion Four Hundred Fifty Eight Million Seven Hundred Sixty Nine Thousand Two Hundred Thirty One and Thirteen One Hundreths
2
3SingleTbl
41One
52Two
63Three
74Four
85Five
96Six
107Seven
118Eight
129Nine
1310Ten
1411Eleven
1512Twelve
1613Thirteen
1714Fourteen
1815Fifteen
1916Sixteen
2017Seventeen
2118Eighteen
2219Nineteen
2320Twenty
2421Twenty One
2522Twenty Two
2623Twenty Three
2724Twenty Four
2825Twenty Five
2926Twenty Six
3027Twenty Seven
3128Twenty Eight
3229Twenty Nine
3330Thirty
3431Thirty One
3532Thirty Two
3633Thirty Three
3734Thirty Four
3835Thirty Five
3936Thirty Six
4037Thirty Seven
4138Thirty Eight
4239Thirty Nine
4340Forty
4441Forty One
4542Forty Two
4643Forty Three
4744Forty Four
4845Forty Five
4946Forty Six
5047Forty Seven
5148Forty Eight
5249Forty Nine
5350Fifty
5451Fifty One
5552Fifty Two
5653Fifty Three
5754Fifty Four
5855Fifty Five
5956Fifty Six
6057Fifty Seven
6158Fifty Eight
6259Fifty Nine
6360Sixty
6461Sixty One
6562Sixty Two
6663Sixty Three
6764Sixty Four
6865Sixty Five
6966Sixty Six
7067Sixty Seven
7168Sixty Eight
7269Sixty Nine
7370Seventy
7471Seventy One
7572Seventy Two
7673Seventy Three
7774Seventy Four
7875Seventy Five
7976Seventy Six
8077Seventy Seven
8178Seventy Eight
8279Seventy Nine
8380Eighty
8481Eighty One
8582Eighty Two
8683Eighty Three
8784Eighty Four
8885Eighty Five
8986Eighty Six
9087Eighty Seven
9188Eighty Eight
9289Eighty Nine
9390Ninety
9491Ninety One
9592Ninety Two
9693Ninety Three
9794Ninety Four
9895Ninety Five
9996Ninety Six
10097Ninety Seven
10198Ninety Eight
10299Ninety Nine
Sheet1
Cell Formulas
RangeFormula
C1C1=IF(A1>=1000000000,IF(A1>=100000000000,VLOOKUP(INT(A1/100000000000),SingleTbl,2,FALSE)&" Hundred"&IF(A1>=1000000000," "&VLOOKUP(MOD(INT(A1/1000000000),100),SingleTbl,2,FALSE)&" Billion","")))& IF(A1>=1000000,IF(A1>=100000000," "&VLOOKUP(INT(MOD(INT(A1/1000000),1000)/100),SingleTbl,2,FALSE)&" Hundred"&IF(A1>1000000," "&VLOOKUP(MOD(INT(A1/1000000),100),SingleTbl,2,FALSE)&" Million","")))& IF(A1>=1000,IF(A1>=100000," "&VLOOKUP(INT(MOD(INT(A1/1000),1000)/100),SingleTbl,2,FALSE)&" Hundred"&IF(A1>=1000," "&VLOOKUP(MOD(INT(A1/1000),100),SingleTbl,2,FALSE)&" Thousand","")))& IF(A1>=1,IF(A1>=100," "&VLOOKUP(MOD(INT(A1/100),10),SingleTbl,2,FALSE)&" Hundred"&IF(A1>=1000," "&VLOOKUP(MOD(INT(A1/1),100),SingleTbl,2,FALSE),"")))& IF(INT((A1-INT(A1))*100)>0," and "&VLOOKUP(INT((A1-INT(A1))*100),SingleTbl,2,FALSE)&" One Hundreths","")
Named Ranges
NameRefers ToCells
SingleTbl=Sheet1!$A$4:$B$102C1
 
Upvote 0
Oops, I jumped the gun before doing some needed testing. This is using the same lookup table posted above

Book1
ABC
13,951,458,769,231.55Three Trillion Nine Hundred Fifty One Billion Four Hundred Fifty Eight Million Seven Hundred Sixty Nine Thousand Two Hundred Thirty One and Fifty Five Cents
Sheet1
Cell Formulas
RangeFormula
C1C1=TRIM(IF(A1>=1000000000000,IF(MOD(INT(A1/1000000000000),1000)>=100,VLOOKUP(INT(MOD(INT(A1/1000000000000),1000)/100),SingleTbl,2,FALSE)&" Hundred","")&IF(MOD(INT(A1/1000000000000),100)>0," "&VLOOKUP(MOD(INT(A1/1000000000000),100),SingleTbl,2,FALSE),"")&" Trillion ","")& IF(A1>=1000000000,IF(MOD(INT(A1/1000000000),1000)>=100,VLOOKUP(INT(MOD(INT(A1/1000000000),1000)/100),SingleTbl,2,FALSE)&" Hundred","")&IF(MOD(INT(A1/1000000000),100)>0," "&VLOOKUP(MOD(INT(A1/1000000000),100),SingleTbl,2,FALSE),"")&" Billion ","")& IF(A1>=1000000,IF(MOD(INT(A1/1000000),1000)>=100,VLOOKUP(INT(MOD(INT(A1/1000000),1000)/100),SingleTbl,2,FALSE)&" Hundred","")&IF(MOD(INT(A1/1000000),100)>0," "&VLOOKUP(MOD(INT(A1/1000000),100),SingleTbl,2,FALSE),"")&" Million ","")& IF(A1>=1000,IF(MOD(INT(A1/1000),1000)>=100,VLOOKUP(INT(MOD(INT(A1/1000),1000)/100),SingleTbl,2,FALSE)&" Hundred ","")&IF(MOD(INT(A1/1000),100)>=0,VLOOKUP(MOD(INT(A1/1000),100),SingleTbl,2,FALSE),"")&" Thousand ","")& IF(A1>=1,IF(MOD(INT(A1/1),1000)>=100,VLOOKUP(MOD(INT(A1/100),10),SingleTbl,2,FALSE)&" Hundred","")&IF(RIGHT(INT(A1),2)<>"00"," "&VLOOKUP(VALUE(RIGHT(INT(A1),2)),SingleTbl,2,FALSE),""))& IF((A1-INT(A1))<>0," and "&VLOOKUP(ROUND((A1-INT(A1))*100,0),SingleTbl,2,FALSE)&" Cents",""))
Named Ranges
NameRefers ToCells
SingleTbl=Sheet1!$A$4:$B$102C1
 
Upvote 0
I created a Lambda function out of it and called it SpellNum

=LAMBDA(CEL,TRIM(IF(CEL>=1000000000000,IF(MOD(INT(CEL/1000000000000),1000)>=100,VLOOKUP(INT(MOD(INT(CEL/1000000000000),1000)/100),SingleTbl,2,FALSE)&" Hundred","")&IF(MOD(INT(CEL/1000000000000),100)>0," "&VLOOKUP(MOD(INT(CEL/1000000000000),100),SingleTbl,2,FALSE),"")&" Trillion ","")& IF(CEL>=1000000000,IF(MOD(INT(CEL/1000000000),1000)>=100,VLOOKUP(INT(MOD(INT(CEL/1000000000),1000)/100),SingleTbl,2,FALSE)&" Hundred","")&IF(MOD(INT(CEL/1000000000),100)>0," "&VLOOKUP(MOD(INT(CEL/1000000000),100),SingleTbl,2,FALSE),"")&" Billion ","")& IF(CEL>=1000000,IF(MOD(INT(CEL/1000000),1000)>=100,VLOOKUP(INT(MOD(INT(CEL/1000000),1000)/100),SingleTbl,2,FALSE)&" Hundred","")&IF(MOD(INT(CEL/1000000),100)>0," "&VLOOKUP(MOD(INT(CEL/1000000),100),SingleTbl,2,FALSE),"")&" Million ","")& IF(CEL>=1000,IF(MOD(INT(CEL/1000),1000)>=100,VLOOKUP(INT(MOD(INT(CEL/1000),1000)/100),SingleTbl,2,FALSE)&" Hundred ","")&IF(MOD(INT(CEL/1000),100)>=0,VLOOKUP(MOD(INT(CEL/1000),100),SingleTbl,2,FALSE),"")&" Thousand ","")& IF(CEL>=1,IF(MOD(INT(CEL/1),1000)>=100,VLOOKUP(MOD(INT(CEL/100),10),SingleTbl,2,FALSE)&" Hundred","")&IF(RIGHT(INT(CEL),2)<>"00"," "&VLOOKUP(VALUE(RIGHT(INT(CEL),2)),SingleTbl,2,FALSE),""))& IF((CEL-INT(CEL))<>0," and "&VLOOKUP(ROUND((CEL-INT(CEL))*100,0),SingleTbl,2,FALSE)&" Cents","")))

Book1
AB
3SingleTbl
41One
52Two
63Three
74Four
85Five
96Six
107Seven
118Eight
129Nine
1310Ten
1411Eleven
1512Twelve
1613Thirteen
1714Fourteen
1815Fifteen
1916Sixteen
2017Seventeen
2118Eighteen
2219Nineteen
2320Twenty
2421Twenty One
2522Twenty Two
2623Twenty Three
2724Twenty Four
2825Twenty Five
2926Twenty Six
3027Twenty Seven
3128Twenty Eight
3229Twenty Nine
3330Thirty
3431Thirty One
3532Thirty Two
3633Thirty Three
3734Thirty Four
3835Thirty Five
3936Thirty Six
4037Thirty Seven
4138Thirty Eight
4239Thirty Nine
4340Forty
4441Forty One
4542Forty Two
4643Forty Three
4744Forty Four
4845Forty Five
4946Forty Six
5047Forty Seven
5148Forty Eight
5249Forty Nine
5350Fifty
5451Fifty One
5552Fifty Two
5653Fifty Three
5754Fifty Four
5855Fifty Five
5956Fifty Six
6057Fifty Seven
6158Fifty Eight
6259Fifty Nine
6360Sixty
6461Sixty One
6562Sixty Two
6663Sixty Three
6764Sixty Four
6865Sixty Five
6966Sixty Six
7067Sixty Seven
7168Sixty Eight
7269Sixty Nine
7370Seventy
7471Seventy One
7572Seventy Two
7673Seventy Three
7774Seventy Four
7875Seventy Five
7976Seventy Six
8077Seventy Seven
8178Seventy Eight
8279Seventy Nine
8380Eighty
8481Eighty One
8582Eighty Two
8683Eighty Three
8784Eighty Four
8885Eighty Five
8986Eighty Six
9087Eighty Seven
9188Eighty Eight
9289Eighty Nine
9390Ninety
9491Ninety One
9592Ninety Two
9693Ninety Three
9794Ninety Four
9895Ninety Five
9996Ninety Six
10097Ninety Seven
10198Ninety Eight
10299Ninety Nine
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

DRSteele
  • Question Question
Formula for spelling out text from numbers for a Chequebook
Replies
4
Views
69
DRSteele
DRSteele
DRSteele
  • Question Question
Spell out numbers into words for Cheques
Replies
3
Views
513
kweaver
kweaver
JohnZ1156
  • Solved
Formula to convert $$$ numbers to text.
Replies
5
Views
783
Joe4
Joe4
G
  • Question Question
use formula in pop-up window
Replies
2
Views
515
Fluff
Fluff
K
  • Solved
Convert Number to Words
Replies
2
Views
910
Kalim Shaikh
K

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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