Convert Number to Words in Excel

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

Share Convert Number to Words in Excel Compute Expert on Twitter

Share Convert Number to Words in Excel Compute Expert on WhatsApp

Share Convert Number to Words in Excel Compute Expert on LinkedIn

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

Click File from menu tab
Click Options
Click Trust Center
Click Trust Center Settings...
Click Macro Settings
Click Enable All Macros choice
Click OK and OK
Click File then Save As
On the Save as Type: dropdown, choose Excel Macro-Enabled Workbook
Name your file in text input column File Name:
Click Save

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

Press Alt and F11 at the same time on your keyboard
Right click on “VBA Project…”
Highlight Insert with your pointer and click Module
Double Click on the Module menu which comes up and input this code in the right side part of the VBA editor screen

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
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!

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

Type equal sign ( = ) in the cell where you want to put the result of the formula to convert number to words in excel
Type NUMBERTOTEXT (can be with large and small letters) and open bracket sign after =
Type digit / cell coordinate where the digit which you want to process is after open bracket sign
Type close bracket sign
Press Enter
The process is done!

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

Language: |

HOW TO USE EXCEL TUTORIAL BLOG - COMPUTE EXPERT

Excel formulas and functions and other usage of excel tutorial which is easy to understand