|
|
|||
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 toolbar button 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 |
Please send me some feedback if you notice any unexpected results from this function.
| Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about obtaining this file |
| file: xlNumbersText.htm | © meadinkent.co.uk 2005 | Page last updated Jun07 |
