 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 Twenty thousand and One point Zero Zero One Seven 2.0672e+06 Two million Sixty Seven thousand Two hundred and Two point Zero Nine 78 Seventy Eight 665.98 Six hundred and Sixty Five point Nine Eight -489 Four hundred and Eighty Nine 105521 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``` Get this information as a documentaccompanied by Excel worksheets Click here for details about obtaining thisfile. It has been rewritten for Excel 2010.

 file: xlNumbersText.htm © meadinkent.co.uk 2014 Page last updated Feb14 CMIDX S3 P5 Y