Spell Numbers into Words

In the Microsoft Excel there is no default function that displays the numbers into words in a worksheet, but you can add this capability by creating your own function by pasting the following WordNum function code into a VBA (Visual Basic for Applications) module. 

This function lets you convert or spell any number into words with a formula, so 4,563.76 would read as Four thousand Five hundred and Sixty Three point Seven Six.

To add this function into your workbook, please follow the beneath steps:

1. Open the workbook where you want to spell the numbers.
2. Press Alt+F11 key from your keyboard to open the Visual Basic Editor (VBE) window.
3. Click the Insert tab in the VBE, and click on the Module option
   
   

   

   VBE - Insert Module
   

   
   
4. You will see a window named YourWorkBook Name - Module1 as shown in the below screenshot:
   
   

   

   Code window in Module
   

   
   
   
   
5. Copy and Paste the following lines of codes in this module:
   

Option Explicit

'Use function "WordNum"

Public Numbers As Variant, Tens As Variant

Private Sub SetNums()
    Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub

Function WordNum(MyNumber As Double) As String
    Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
    Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
    ' This macro was written by Chris Mead - www.MeadInKent.co.uk
    If Abs(MyNumber) > 999999999 Then
        WordNum = "Value too large"
        Exit Function
    End If
    SetNums
    ' String representation of amount (excl decimals)
    NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
    ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))
    For n = 3 To 1 Step -1    'analyse the absolute number as 3 sets of 3 digits
        StrNo = Format(ValNo(n), "000")
        If ValNo(n) > 0 Then
            Temp1 = GetTens(Val(Right(StrNo, 2)))
            If Left(StrNo, 1) <> "0" Then
                Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
                If Temp1 <> "" Then Temp2 = Temp2 & " and "
            Else
                Temp2 = ""
            End If
            If n = 3 Then
                If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
                WordNum = Trim(Temp2 & Temp1)
            End If
            If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
            If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)
        End If
    Next n
    NumStr = Trim(Str(Abs(MyNumber)))
    ' Values after the decimal place
    DecimalPosition = InStr(NumStr, ".")
    Numbers(0) = "Zero"
    If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
        Temp1 = " point"
        For n = DecimalPosition + 1 To Len(NumStr)
            Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
        Next n
        WordNum = WordNum & Temp1
    End If
    If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
        WordNum = "Zero" & WordNum
    End If
End Function

Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
    If TensNum <= 19 Then
        GetTens = Numbers(TensNum)
    Else
        Dim MyNo As String
        MyNo = Format(TensNum, "00")
        GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
    End If
End Function

6. Press Ctrl+S to save the updated workbook.
   
   Note: When you try to save the workbook with a macro you'll get the message "The following features cannot be saved in macro-free workbook"
   
   

   

   Cannot Save VB Project

   
   
   
   Click No. When you see a new dialog, chose the Save As option. In the field "Save as type" pick the option "Excel macro-enabled workbook".
   
   

   

   SaveAs Macro Enabled Excel Workbook
   

   
   Congratulations! Now, you have added the WordNum UDF Function to your workbook successfully.
   

Use WordNum function in your worksheets

Now you can use the function WordNum in your Excel Workbook. Enter =WordNum(A22) into the cell where you need to get the number written in words. Here A22 is the address of the cell with the number. 

 

WordNum Function Example

Other Related UDF Functions:

UDF to Spell INR Amount into Words

UDF to Spell USD Amount into Words