MeadInKent
Home
Excel
Access
| Feedback | Differences between Dates | Link chart titles to cell contents |

How to display a number as words in Excel

A VBA user defined macro function

Excel does not contain a built in function that will display a number as a series of words (i.e. a text string) but a search in Google will uncover several user defined functions to perform this task. They can each produce slightly different results and may be suitable for different purposes.

A user defined function called WordNum(value) will accept almost any number value and convert it into a long string of words in written English. The following examples show the results that can be obtained from the function. The value can be any number up to 999,999,999.


20001.0017 Twenty thousand and One point Zero Zero One Seven
2067202.0900 Two million Sixty Seven thousand Two hundred and Two point Zero Nine
78.0000 Seventy Eight
665.9800 Six hundred and Sixty Five point Nine Eight
-489.0000 Four hundred and Eighty Nine
105521.0000 One hundred and Five thousand Five hundred and Twenty One

The WordNum() function requires the following code to be copied and pasted in a Visual Basic module. Once the code has been added to a workbook, the Paste Function option should contain a new User Defined function called WordNum(). It is available to convert any values or cells containing a value into words.

Option Explicit
Public Numbers As Variant, Tens As Variant

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

excel Get this information as a document
accompanied by Excel worksheets		 Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.
file: xlNumbersText.htm © meadinkent.co.uk 2014 Page last updated Feb14 CMIDX S3 P5 Y