# Thread: Showing Numbers in English Words (2003)

1. ## Showing Numbers in English Words (2003)

I have searched these boards and Google and I just want to make sure that I'm not missing anything here because I am quite amazed by this fact. From what I understand Excel does not offer either of the following 2 abilities:

1) Convert a number to it's emglish format. For example convert the number 1 to the English value one or the number 62 to the English word Sixty-two or even Sixty two.

2) Autofill numbers represented as English Words. For example if in 3 consecutive cells in a row I have the following 3 values: One, Two & Three, Excel's Autofill is unable to figure out that the value in the next cell should be FOur.

I'm just looking for confirmation that the above 2 statements are correct. I ask because I'm just amazed that with all of it's whiz bang features that Excel is still unable to do either of these. #1 in particular is quite puzzling to me. I have seen various code on the internet for this including Microsoft's own KB article # 210586. Even after publishing it's own method for doing this as far back as Office 2000 there is still no built-in methid in Excel to convert numbers to English Words.

Thanks
Ed

2. ## Re: Showing Numbers in English Words (2003)

There is no built-in facility for either. As mentioned you could create a function (and MS has listed one) for converting. The second could be done but you would have to create the custom list yourself.

Steve

3. ## Re: Showing Numbers in English Words (2003)

You are correct in number 1, Excel does not have any built in function to convert numbers to text. The VBA code below is what I use to do the conversion. You use it like this if the number is in A1:

<code>
=NumberToText(A1)
</code>

to get "Sixty Two" if A1 contains 62.

If you are doing Dollars and cents, then you can do this:

<code>
=NumberToText(A1,"Dollars","Cents")
</code>

to get "Sixty Two Dollars and Fifty Cents" if A1 contains 62.5.

<code>
Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional vCent As Variant) As Variant
Dim TMBT As Variant
Dim sNum As String, sDec As String, sHun As String, IC As Integer
Dim Result As String, sCurName As String, sCent As String

If Application.IsNumber(Num) = False Then
NumberToText = CVErr(xlValue)
Exit Function
End If

If IsMissing(vCurName) Then
sCurName = ""
Else
sCurName = Trim(CStr(vCurName))
End If
If IsMissing(vCent) Then
sCent = ""
Else
sCent = Trim(CStr(vCent))
End If

TMBT = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion")

If IsMissing(sCent) Or IsNull(sCent) Then
sNum = Format(Application.Round(Num, 0), "0")
Else
sNum = Format(Application.Round(Num, 2), "0.00")
sDec = Right(sNum, 2)
sNum = Left(sNum, Len(sNum) - 3)
If CInt(sDec) <> 0 Then
sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent)
Else
sDec = ""
End If
End If

IC = 0
While Len(sNum) > 0
sHun = Right(sNum, 3)
sNum = Left(sNum, Application.Max(Len(sNum) - 3, 0))
If CInt(sHun) <> 0 Then
Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " & Result)
End If
IC = IC + 1
Wend
Result = Trim(Result & " " & sCurName)
Result = Trim(Result & " " & sDec)

NumberToText = Result

End Function

Function HundredsToText(Num As Integer) As String
Dim Units As Variant, Teens As Variant, Tens As Variant
Dim I As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer
Dim Result As String

Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")

Result = ""
IUnit = Num Mod 10
I = Int(Num / 10)
ITen = I Mod 10
IHundred = Int(I / 10)
If IHundred > 0 Then
Result = Units(IHundred) & " Hundred"
End If
If ITen = 1 Then
Result = Result & " " & Teens(IUnit)
Else
If ITen > 1 Then
Result = Trim(Result & " " & Tens(ITen) & " " & Units(IUnit))
Else
Result = Trim(Result & " " & Units(IUnit))
End If
End If

HundredsToText = Result

End Function

</code>

Excel can do what you ask in your second question. If you select Options from the Tools menu, and then click on the "Custom Lists" tab, you can define a list of the text numbers. If you do this, then Excel will be able to do what you asked. However, the range of numbers will be limited to the list you define.

4. ## Re: Showing Numbers in English Words (2003)

What you've done with the UDF and VBA is very good. However, is there a way to insert the grammatically correct "-" where appropriate or is that difficult or impossibe?
For example: thirty-six rather than thirty six.

5. ## Re: Showing Numbers in English Words (2003)

Does this do what you want:

<pre>Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional vCent As Variant) As Variant
Dim TMBT As Variant
Dim sNum As String, sDec As String, sHun As String, IC As Integer
Dim Result As String, sCurName As String, sCent As String

If Application.IsNumber(Num) = False Then
NumberToText = CVErr(xlValue)
Exit Function
End If

If IsMissing(vCurName) Then
sCurName = ""
Else
sCurName = Trim(CStr(vCurName))
End If
If IsMissing(vCent) Then
sCent = ""
Else
sCent = Trim(CStr(vCent))
End If

TMBT = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion")

If IsMissing(sCent) Or IsNull(sCent) Then
sNum = Format(Application.Round(Num, 0), "0")
Else
sNum = Format(Application.Round(Num, 2), "0.00")
sDec = Right(sNum, 2)
sNum = Left(sNum, Len(sNum) - 3)
If CInt(sDec) <> 0 Then
sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent)
Else
sDec = ""
End If
End If

IC = 0
While Len(sNum) > 0
sHun = Right(sNum, 3)
sNum = Left(sNum, Application.Max(Len(sNum) - 3, 0))
If CInt(sHun) <> 0 Then
Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " & Result)
End If
IC = IC + 1
Wend
Result = Trim(Result & " " & sCurName)
Result = Trim(Result & " " & sDec)

NumberToText = Result

End Function

Function HundredsToText(Num As Integer) As String
Dim Units As Variant, Teens As Variant, Tens As Variant
Dim I As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer
Dim Result As String

Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")

Result = ""
IUnit = Num Mod 10
I = Int(Num / 10)
ITen = I Mod 10
IHundred = Int(I / 10)
If IHundred > 0 Then
Result = Units(IHundred) & " Hundred"
End If
If ITen = 1 Then
Result = Result & " " & Teens(IUnit)
Else
If ITen > 1 Then
Result = Trim(Result & " " & Tens(ITen) & "-" & Units(IUnit))
Else
Result = Trim(Result & " " & Units(IUnit))
End If
End If
If Right(Result, 1) = "-" Then Result = Left(Result, Len(Result) - 1)

HundredsToText = Result

End Function
</pre>

6. ## Re: Showing Numbers in English Words (2003)

Legare,
First let me thank you for your contribution to this post. I appreciate your posting your code even after a working version was mentioned. Your code looks shorter and cleaner. Do you know if it performs better then the code from Microsoft's KB Article? I also wanted to mention that when I try to copy the second instance of your code it copies as a single line where as the first has carriage returns and copies and pastes exactly as it looks. Are the differences simple enough for you to quickly pint out? I can reformat the second code instance if need be but I figured if the changes were small enough it would be easier if you could just point them out.

Lastly I wanted to know if it is OK to use your code? I have a client who uses an Excel Add-in my company produces and they are looking for a way to convert some of the cells values from numbers to their English Words. I figured it is OK to pass this code along to them since you posted it however I felt it was the proper thing to check with you first. I prefer explicit to implicit any day.

A big thanks to Steve and Kevin(KWeaver); Appreaciate you guys replies too.

Thanks Again!
Ed

7. ## Re: Showing Numbers in English Words (2003)

Perfectly! Thanks for the update.

8. ## Re: Showing Numbers in English Words (2003)

The code pastes as one line because it is HTML when you copy it from the lounge and the VBA editor doesn't handle that very well. There is a way to post code on the lounge board that can be copied and pasted, but there is a bug in the lounge code that handles that which makes it very difficult to post larger blocks of code in that format (you get an error when you try to post the code). Therefore, I had to post that block of code in another format that displays OK, but won't directly copy and paste. To copy and paste that block of code, copy it as usual and then paste it into either Word or WordPad. Copy the code from there and paste it into the VBA editor. It should paste as displayed in the lounge.

You are free to use any code that I post in the Lounge, as long as you do not sell it. You can give a copy to your client, but you can not sell it to them.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•