Excel is an extremely powerful tool to calculate and handle accounting formatted data. When working with accounting, sometimes we need to convert the currency to words for various purposes. Now, if you are looking forward to doing this, you have come to a perfect place. In this article, I will demonstrate the step-by-step guidelines to convert currency to words in Excel.
Download Practice Workbook
You can download our practice workbook from here for free!
Steps to Convert Currency to Words in Excel
Say, we have a dataset of several numbers that you want to convert into words.
Now, the best way to convert the currency to words in Excel is to create a function using a VBA code. Follow the step-by-step guidelines to accomplish your desired result.
📌 Step 1: Create a Module
You need to access the VBA window first to write the VBA code.
- In order to do this, first and foremost, go to the Developer tab >> Visual Basic tool.
- As a result, the Microsoft Visual Basic for Applications window will appear.
- Following, click on the Insert tab >> Module option.
Consequently, a new module named Module 1 will be created under the Modules group.
Read More: How to Change Default Currency in Excel (4 Simple Methods)
📌 Step 2: Write VBA Code and Save the Module
Now, you have to write the VBA code in the module.
- For doing this, double-click on Module 1.
- Consequently, the code window will appear. Write the required VBA code here.
Function ConvertCurrencytoWords(ByVal GivenCurrency)
Dim USD, C
Words = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
GivenCurrency = Trim(Str(GivenCurrency))
FractionCurrency = InStr(GivenCurrency, ".")
If FractionCurrency > 0 Then
C = TensPlace(Left(Mid(GivenCurrency, FractionCurrency + 1) & "00", 2))
GivenCurrency = Trim(Left(GivenCurrency, FractionCurrency - 1))
End If
GetIndex = 1
Do While GivenCurrency <> ""
GetHundred = ""
GetValue = Right(GivenCurrency, 3)
If Val(GetValue) <> 0 Then
GetValue = Right("000" & GetValue, 3)
If Mid(GetValue, 1, 1) <> "0" Then
GetHundred = OnesPlace(Mid(GetValue, 1, 1)) & " Hundred "
End If
If Mid(GetValue, 2, 1) <> "0" Then
GetHundred = GetHundred & TensPlace(Mid(GetValue, 2))
Else
GetHundred = GetHundred & OnesPlace(Mid(GetValue, 3))
End If
End If
If GetHundred <> "" Then
USD = GetHundred & Words(GetIndex) & USD
End If
If Len(GivenCurrency) > 3 Then
GivenCurrency = Left(GivenCurrency, Len(GivenCurrency) - 3)
Else
GivenCurrency = ""
End If
GetIndex = GetIndex + 1
Loop
Select Case USD
Case ""
USD = "No USD"
Case "One"
USD = "One Dollar"
Case Else
USD = USD & " USD"
End Select
Select Case C
Case ""
C = " and No C"
Case "One"
C = " and One Cent"
Case Else
C = " and " & C & " C"
End Select
ConvertCurrencytoWords = USD & C
End Function
Function TensPlace(TensDigit)
Dim Output As String
Output = ""
If Val(Left(TensDigit, 1)) = 1 Then
Select Case Val(TensDigit)
Case 10: Output = "Ten"
Case 11: Output = "Eleven"
Case 12: Output = "Twelve"
Case 13: Output = "Thirteen"
Case 14: Output = "Fourteen"
Case 15: Output = "Fifteen"
Case 16: Output = "Sixteen"
Case 17: Output = "Seventeen"
Case 18: Output = "Eighteen"
Case 19: Output = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(TensDigit, 1))
Case 2: Output = "Twenty "
Case 3: Output = "Thirty "
Case 4: Output = "Forty "
Case 5: Output = "Fifty "
Case 6: Output = "Sixty "
Case 7: Output = "Seventy "
Case 8: Output = "Eighty "
Case 9: Output = "Ninety "
Case Else
End Select
Output = Output & OnesPlace(Right(TensDigit, 1))
End If
TensPlace = Output
End Function
Function OnesPlace(OnesDigit)
Select Case Val(OnesDigit)
Case 1: OnesPlace = "One"
Case 2: OnesPlace = "Two"
Case 3: OnesPlace = "Three"
Case 4: OnesPlace = "Four"
Case 5: OnesPlace = "Five"
Case 6: OnesPlace = "Six"
Case 7: OnesPlace = "Seven"
Case 8: OnesPlace = "Eight"
Case 9: OnesPlace = "Nine"
Case Else: OnesPlace = ""
End Select
End Function
- Afterward, press Ctrl + S and close the VBA window.
- Subsequently, go to the File tab of Excel.
- Following, choose the Save As option from the expanded File tab.
- As a result, the Excel Save As window will appear. Following, click on the Browse option.
- At this time, the Save As dialogue box will appear. Select the Save as type: option as .xlsm format. Finally, click on the Save button.
Thus, you have created a new module named ConvertCurrencytoWords through the VBA code.
Read More: Excel VBA: Format Currency to Two Decimal Places (3 Methods)
Similar Readings
- How to Add Currency Symbol in Excel (6 Ways)
- Remove Currency Symbol in Excel (6 Ways)
- How to Concatenate and Keep Currency Format in Excel (3 Methods)
📌 Step 3: Insert the User Defined Function
As you have created a module, now you can use it as a function here to get your desired result.
- Initially, click on the C5 cell.
- Afterward, insert the following formula containing the created function from Module 1.
=ConvertCurrencytoWords(B5)
- Subsequently, hit the Enter button.
- As a result, you will be able to convert the currency of the B5 cell to words in Excel.
- Now, place your cursor in the bottom right position of the cell.
- Subsequently, drag the fill handle below upon its appearance.
As a result, you will get all the converted words from the currency of the whole dataset. And, the output should look like the following.
Convert Integer Currency to Words by Using an Excel Formula
You can also use a formula to convert the currency to words in Excel. But, in this way, you can only convert the integer numbers properly. Decimal conversion can result in errors in this formula. But the advantage of this formula is, that you can convert integers up to 100 billion using this formula. (We have found the formula from HaroonSid.)
Follow the steps below to achieve this.
📌 Steps:
- First and foremost, click on the C5 cell and insert the following formula.
=IF(OR(LEN(FLOOR(B5,1))=13,FLOOR(B5,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(B5>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(B5,1)>1,"",""))&IF(ISERROR(FIND(".",B5,1)),""," "&PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&"","")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")&"","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine")&"",IF(LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),2)="01","one cent",IF(LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet1!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine")&"","")))))))
- Subsequently, hit the Enter button.
- Afterward, place your cursor in the bottom right position of the cell.
- Drag the fill handle downward upon its appearance.
Thus, you will get all the currencies converted into words. For instance, the output should look like this.
💬 Things to Remember
- You must save the Excel file in .xlsm format. Otherwise, the macro won’t be enabled. Thus, you can not use the ConvertCurrencytoWords function.
- Another thing, you have to keep in mind is that, if you send the Excel file to someone, the macro has to be built in his/her Excel. Moreover, he/she has to enable the macro when opening the file. Otherwise, he won’t be able to use the created function.
Conclusion
In a nutshell, in this article, I have shown you step-by-step guidelines to convert the currency to words in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. If you have any further queries or recommendations, feel free to leave a comment below.
You can visit ExcelDemy to learn so much more about Excel. Have a good day! Thank you!
extremely useful!
Hi Bernadeth,
Thanks for your appreciation. To get more useful content kindly visit our site.
Thanks
Regards
ExcelDemy