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:
- Open the workbook where you want to spell the numbers.
-
Press Alt+F11 key from your keyboard to open the
Visual Basic Editor (VBE) window.
-
Click the Insert tab in the VBE, and click
on the Module option
|
VBE - Insert Module
|
-
You will see a window named YourWorkBook Name - Module1 as shown in
the below screenshot:
|
Code window in Module
|
- 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
-
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
|
