Convert Number to Words in Excel


Home >> How to Use Excel Tutorial >> Excel Tips and Trick >> Convert Number to Words in Excel



In this part of our tutorial blog, you will learn and understand about utilizing excel formula to convert number to words. Convert number to words in excel is not a built-in feature in the software. Because of that, what will be given here is the VBA code to create a customized formula so the automation process to convert number to words in excel can exist. After putting the VBA code in your spreadsheet, you can then use the formula from the code just like any other formula in the software.


Why We Need to Learn About Excel Convert Number to Words?


When working on data in the form of digits, especially related to money, often we need to make the word form of the digit. For one or two static digit data in a simple form, it will probably be not too much of a problem to do it manually. But when you face dynamic digits of money that keep changing, then it will be much more effective and efficient if there is a tool that can help to make the process automatic.

If we need to do that kind of digit processing in this spreadsheet software, there is no special formula to help the conversion process from a digit to words. But what can help us with this matter is we can create our own formula in excel according to our needs. Therefore, if we often need to convert number to words in excel, then the formulation of an excel formula for that matter will become important for us to do and understand. Thus, Compute Expert provides the VBA code in this tutorial for customized excel convert number to words facilitation which can be taken for you to be used in your spreadsheet.


What is Convert Number to Words in Excel?


Convert number to words in excel is a customized formula from VBA code which can be used to convert figures to words in excel, usually needed if we process digits related to money. After inputting the VBA code given below in your spreadsheet, the formula to convert number to words in excel can be used just like any regular formula with the input of the formula is the digit to be processed.

Briefly, the input of this customized formula is explained as follows:


=NUMBERTOTEXT(value)


Keterangan:
value = the number that you want to convert to words


How to Create and Use Formula for Number to Words in Excel?


Next part will explain about how to formulate the formula for the amount in words in excel facilitation in your VBA editor and how to use it


Check and Set Macro Security Setting

To be able to use a customized formula from a VBA code, you need to permit macro to be able to function in your excel. Therefore, we need to make sure about this permission setting first before you can input the code to create a formula to convert number to words in excel

  1. Click File from menu tab

    Convert Number to Words in Excel - Screenshot of Step 1-1

  2. Click Options

    Convert Number to Words in Excel - Screenshot of Step 1-2

  3. Click Trust Center

    Convert Number to Words in Excel - Screenshot of Step 1-3

  4. Click Trust Center Settings...

    Convert Number to Words in Excel - Screenshot of Step 1-4

  5. Click Macro Settings

    Convert Number to Words in Excel - Screenshot of Step 1-5

  6. Click Enable All Macros choice

    Convert Number to Words in Excel - Screenshot of Step 1-6

  7. Click OK and OK

    Convert Number to Words in Excel - Screenshot of Step 1-7-1

    Convert Number to Words in Excel - Screenshot of Step 1-7-2

  8. Click File then Save As

    Convert Number to Words in Excel - Screenshot of Step 1-8

  9. On the Save as Type: dropdown, choose Excel Macro-Enabled Workbook

    Convert Number to Words in Excel - Screenshot of Step 1-9

  10. Name your file in text input column File Name:

    Convert Number to Words in Excel - Screenshot of Step 1-10

  11. Click Save

    Convert Number to Words in Excel - Screenshot of Step 1-11




Create the Formula to Convert Number to Words in Excel

After setting the macro permission, it is time to create excel convert number to words formula in your file by using VBA editor

  1. Press Alt and F11 at the same time on your keyboard
  2. Right click on “VBA Project…”

    Convert Number to Words in Excel - Screenshot of Step 2-2

  3. Highlight Insert with your pointer and click Module

    Convert Number to Words in Excel - Screenshot of Step 2-3

  4. Double Click on the Module menu which comes up and input this code in the right side part of the VBA editor screen

    Convert Number to Words in Excel - Screenshot of Step 2-4

    Function NUMBERTOTEXT(ByVal n As Currency) As String    

      Const Thousand = 1000@    
      Const Million = Thousand * Thousand    
      Const Billion = Thousand * Million    
      Const Trillion = Thousand * Billion    

      If (n = 0@) Then NUMBERTOTEXT = "zero": Exit Function    

      Dim Buf As String: If (n < 0@) Then Buf = "negative " Else Buf = ""    
      Dim Frac As Currency: Frac = Abs(n - Fix(n))    
      If (n < 0@ Or Frac <> 0@) Then n = Abs(Fix(n))    
      Dim AtLeastOne As Integer: AtLeastOne = n >= 1    

      If (n >= Trillion) Then      
        Buf = Buf & EnglishDigitGroup(Int(n / Trillion)) & " trillion"      
        n = n - Int(n / Trillion) * Trillion      
        If (n >= 1@) Then Buf = Buf & " "    
      End If    

      If (n >= Billion) Then      
        Buf = Buf & EnglishDigitGroup(Int(n / Billion)) & " billion"      
        n = n - Int(n / Billion) * Billion      
        If (n >= 1@) Then Buf = Buf & " "    
      End If    

      If (n >= Million) Then      
        Buf = Buf & EnglishDigitGroup(n \ Million) & " million"      
        n = n Mod Million      
        If (n >= 1@) Then Buf = Buf & " "    
      End If    

      If (n >= Thousand) Then      
        Buf = Buf & EnglishDigitGroup(n \ Thousand) & " thousand"      
        n = n Mod Thousand      
        If (n >= 1@) Then Buf = Buf & " "    
      End If    

      If (n >= 1@) Then      
        Buf = Buf & EnglishDigitGroup(n)    
      End If    

      If (Frac = 0@) Then      
        Buf = Buf    
      ElseIf (Int(Frac * 100@) = Frac * 100@) Then      
        If AtLeastOne Then Buf = Buf & " "      
        Buf = Buf & Format$(Frac * 100@, "00") & "/100"    
      Else      
        If AtLeastOne Then Buf = Buf & " "      
        Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"    
      End If    

      NUMBERTOTEXT = Buf
    End Function

    Private Function EnglishDigitGroup(ByVal n As Integer) As String    

      Const Hundred = "hundred"    
      Const One = "one "    
      Const Two = "two "    
      Const Three = "three "    
      Const Four = "four "    
      Const Five = "five "    
      Const Six = "six "    
      Const Seven = "seven "    
      Const Eight = "eight "    
      Const Nine = "nine "    
      Dim Buf As String: Buf = ""    
      Dim Flag As Integer: Flag = False    

      Select Case (n \ 100)      
        Case 0: Buf = "": Flag = False      
        Case 1: Buf = One & Hundred: Flag = True      
        Case 2: Buf = Two & Hundred: Flag = True      
        Case 3: Buf = Three & Hundred: Flag = True      
        Case 4: Buf = Four & Hundred: Flag = True      
        Case 5: Buf = Five & Hundred: Flag = True      
        Case 6: Buf = Six & Hundred: Flag = True      
        Case 7: Buf = Seven & Hundred: Flag = True      
        Case 8: Buf = Eight & Hundred: Flag = True      
        Case 9: Buf = Nine & Hundred: Flag = True    
      End Select    

      If (Flag <> False) Then n = n Mod 100    
      If (n > 0) Then      
        If (Flag <> False) Then Buf = Buf & " "    
      Else      
        EnglishDigitGroup = Buf      
        Exit Function    
      End If    

      Select Case (n \ 10)      
        Case 0, 1: Flag = False      
        Case 2: Buf = Buf & "twenty": Flag = True      
        Case 3: Buf = Buf & "thirty": Flag = True      
        Case 4: Buf = Buf & "forty": Flag = True      
        Case 5: Buf = Buf & "fifty": Flag = True      
        Case 6: Buf = Buf & "sixty": Flag = True      
        Case 7: Buf = Buf & "seventy": Flag = True      
        Case 8: Buf = Buf & "eighty": Flag = True      
        Case 9: Buf = Buf & "ninety": Flag = True    
      End Select    

      If (Flag <> False) Then n = n Mod 10    
      If (n > 0) Then      
        If (Flag <> False) Then Buf = Buf & " "    
      Else      
        EnglishDigitGroup = Buf      
        Exit Function    
      End If    

      Select Case (n)      
        Case 0:      
        Case 1: Buf = Buf & "one"      
        Case 2: Buf = Buf & "two"      
        Case 3: Buf = Buf & "three"      
        Case 4: Buf = Buf & "four"      
        Case 5: Buf = Buf & "five"      
        Case 6: Buf = Buf & "six"      
        Case 7: Buf = Buf & "seven"      
        Case 8: Buf = Buf & "eight"      
        Case 9: Buf = Buf & "nine"      
        Case 10: Buf = Buf & "ten"      
        Case 11: Buf = Buf & "eleven"      
        Case 12: Buf = Buf & "twelve"      
        Case 13: Buf = Buf & "thirteen"      
        Case 14: Buf = Buf & "fourteen"      
        Case 15: Buf = Buf & "fifteen"      
        Case 16: Buf = Buf & "sixteen"      
        Case 17: Buf = Buf & "seventeen"      
        Case 18: Buf = Buf & "eighteen"      
        Case 19: Buf = Buf & "nineteen"    
       End Select    

       EnglishDigitGroup = Buf

    End Function


  5. Click on the X mark in the red box on the most top right to close the VBA editor. The formula to convert number to words in excel is now ready to use!

    Convert Number to Words in Excel - Screenshot of Step 2-5




Use Excel Convert Number to Words Formula

After inputting the code to create the formula to convert number to words in excel, it is time to use it

  1. Type equal sign ( = ) in the cell where you want to put the result of the formula to convert number to words in excel

    Convert Number to Words in Excel - Screenshot of Step 3-1

  2. Type NUMBERTOTEXT (can be with large and small letters) and open bracket sign after =

    Convert Number to Words in Excel - Screenshot of Step 3-2

  3. Type digit / cell coordinate where the digit which you want to process is after open bracket sign

    Convert Number to Words in Excel - Screenshot of Step 3-3

  4. Type close bracket sign

    Convert Number to Words in Excel - Screenshot of Step 3-4

  5. Press Enter
  6. The process is done!

    Convert Number to Words in Excel - Screenshot of Step 3-6



Exercise


After you have learned how to convert number to words in excel, you can practice your understanding about it through this exercise!

Download the file from the following link and do as instructed. Download the answer if you have done the exercise and sure about the results!

Link to download the exercise file:
Download here

Instruction:


Use excel convert number to words function to read the digits in first, second, and third columns!
Link to download answer:
Download here


Additional Notes

  • This formula to convert number to words in excel cannot be used to read decimal digits
  • This formula to convert number to words in excel is not automatically there every time you open this spreadsheet software after the first time you input VBA code to create it. If you want to use it in other files, you need to input it again by redoing the steps in this tutorial




Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert