Table of Contents
How to Convert Number to Words
Creating a custom VBA function is a common way to convert number to words in Excel. Here’s how you can do it:
STEP 1: Click the Visual Basic option in the Developer tab.
STEP 2: Click on Insert
in the menu, then select Module
. This will create a new module.
STEP 3: Copy and paste the following VBA code into the module:
Function NumberToWords(ByVal MyNumber)<br /> Dim Units As String<br /> Dim SubUnits As String<br /> Dim TempStr As String<br /> Dim DecimalPlace As Integer<br /> Dim Count As Integer<br /> Dim Place(9) As String<br /> Place(2) = " Thousand "<br /> Place(3) = " Million "<br /> Place(4) = " Billion "<br /> Place(5) = " Trillion "<br /> ' Convert MyNumber to string and trim white space<br /> MyNumber = Trim(CStr(MyNumber))<br /> ' Find Position of decimal place (if any) in MyNumber<br /> DecimalPlace = InStr(MyNumber, ".")<br /> ' Convert SubUnits and set MyNumber to Units amount<br /> If DecimalPlace > 0 Then<br /> SubUnits = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))<br /> MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))<br /> End If<br /> Count = 1<br /> Do While MyNumber <> ""<br /> TempStr = GetHundreds(Right(MyNumber, 3))<br /> If TempStr <> "" Then Units = TempStr & Place(Count) & Units<br /> If Len(MyNumber) > 3 Then<br /> MyNumber = Left(MyNumber, Len(MyNumber) - 3)<br /> Else<br /> MyNumber = ""<br /> End If<br /> Count = Count + 1<br /> Loop<br /> NumberToWords = Application.Trim(Units)<br /> End Function</p> <p>Function GetHundreds(ByVal MyNumber)<br /> Dim Result As String<br /> If Val(MyNumber) = 0 Then Exit Function<br /> MyNumber = Right("000" & MyNumber, 3)<br /> ' Convert the hundreds place.<br /> If Mid(MyNumber, 1, 1) <> "0" Then<br /> Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "<br /> End If<br /> ' Convert the tens and ones place.<br /> If Mid(MyNumber, 2, 1) <> "0" Then<br /> Result = Result & GetTens(Mid(MyNumber, 2))<br /> Else<br /> Result = Result & GetDigit(Mid(MyNumber, 3))<br /> End If<br /> GetHundreds = Result<br /> End Function</p> <p>Function GetTens(TensText)<br /> Dim Result As String<br /> Result = "" ' Null out the temporary function value.<br /> If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...<br /> Select Case Val(TensText)<br /> Case 10: Result = "Ten"<br /> Case 11: Result = "Eleven"<br /> Case 12: Result = "Twelve"<br /> Case 13: Result = "Thirteen"<br /> Case 14: Result = "Fourteen"<br /> Case 15: Result = "Fifteen"<br /> Case 16: Result = "Sixteen"<br /> Case 17: Result = "Seventeen"<br /> Case 18: Result = "Eighteen"<br /> Case 19: Result = "Nineteen"<br /> Case Else<br /> End Select<br /> Else ' If value between 20-99...<br /> Select Case Val(Left(TensText, 1))<br /> Case 2: Result = "Twenty "<br /> Case 3: Result = "Thirty "<br /> Case 4: Result = "Forty "<br /> Case 5: Result = "Fifty "<br /> Case 6: Result = "Sixty "<br /> Case 7: Result = "Seventy "<br /> Case 8: Result = "Eighty "<br /> Case 9: Result = "Ninety "<br /> Case Else<br /> End Select<br /> Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.<br /> End If<br /> GetTens = Result<br /> End Function<br /> Function GetDigit(Digit)<br /> Select Case Val(Digit)<br /> Case 1: GetDigit = "One"<br /> Case 2: GetDigit = "Two"<br /> Case 3: GetDigit = "Three"<br /> Case 4: GetDigit = "Four"<br /> Case 5: GetDigit = "Five"<br /> Case 6: GetDigit = "Six"<br /> Case 7: GetDigit = "Seven"<br /> Case 8: GetDigit = "Eight"<br /> Case 9: GetDigit = "Nine"<br /> Case Else: GetDigit = ""<br /> End Select<br /> End Function
f
STEP 4: Press Ctrl + S
to save your workbook as a macro-enabled workbook (.xlsm).
STEP 5: Close the VBA editor by clicking the X
in the top-right corner or by pressing Alt + Q
.
STEP 6: In your Excel worksheet, you can now use the NumberToWords
function just like any other Excel function. For example, if you want to convert the number in cell A2
to words, you would enter the following formula:
=NumberToWords(A2)
Conclusion
Converting numbers to words in Excel can be easily accomplished using VBA code. Each method has its advantages, and the best choice will depend on your specific needs and familiarity with Excel’s advanced features. By following the steps outlined above, you can enhance your Excel documents with clear, readable text representations of numerical values.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.