to make the digit 10 into the text ten

T

Tbarbee708

can i type in a numer (ie 10) in A1 and have A2 fill in automatically the
written "ten"? etc
 
G

Gary''s Student

You can do this without changing the programming.

With 10 in A1 and ten in B1 enter in C1:
=B1 & " dollars and cents"
 
G

Gord Dibben

You will have to change the code if you don't want the "dollars and cents"
returned.

Try this revision..........but it doesn't do decimals, only whole numbers.


Function SpellNumber(ByVal n As Double, _
Optional ByVal useword As Boolean = True, _
Optional ByVal cents As String = "", _
Optional ByVal join As String = " And", _
Optional ByVal fraction As Boolean = False) As String


Dim myLength As Long
Dim i As Long
Dim myNum As Long
Dim Remainder As Long

SpellNumber = ""
Remainder = Round(100 * (n - Int(n)), 0)

myLength = Int(Application.Log10(n) / 3)

For i = myLength To 0 Step -1
myNum = Int(n / 10 ^ (i * 3))
n = n - myNum * 10 ^ (i * 3)
If myNum > 0 Then
SpellNumber = SpellNumber & MakeWord(Int(myNum)) & _
Choose(i + 1, "", " thousand ", " million ", " billion ", " trillion ")
End If
Next i
SpellNumber = SpellNumber & IIf(useword, " " & ccy, "")
SpellNumber = Application.Proper(Trim(SpellNumber))

End Function

Function MakeWord(ByVal inValue As Long) As String
Dim unitWord, tenWord
Dim n As Long
Dim unit As Long, ten As Long, hund As Long

unitWord = Array("", "one", "two", "three", "four", _
"five", "six", "seven", "eight", _
"nine", "ten", "eleven", "twelve", _
"thirteen", "fourteen", "fifteen", _
"sixteen", "seventeen", "eighteen", "nineteen")
tenWord = Array("", "ten", "twenty", "thirty", "forty", _
"fifty", "sixty", "seventy", "eighty", "ninety")
MakeWord = ""
n = inValue
If n = 0 Then MakeWord = "zero"
hund = n \ 100
If hund > 0 Then MakeWord = MakeWord & MakeWord(Int(hund)) _
& " hundred and "
n = n - hund * 100
If n < 20 Then
ten = n
MakeWord = MakeWord & unitWord(ten) & " "
Else
ten = n \ 10
MakeWord = MakeWord & tenWord(ten) & " "
unit = n - ten * 10
MakeWord = Trim(MakeWord & unitWord(unit))
End If
MakeWord = Application.Proper(Trim(MakeWord))

End Function


Gord Dibben MS Excel MVP
 
Top