convert a number to text (in english)

  • Thread starter Thread starter Costas
  • Start date Start date
C

Costas

How can i convert a number to text , but in english (for
example, i have the number 12345 stored on cell A1 and i
want to somehow have the text "Twelve thousand and three
hunderd and fourty five" in another cell, for example
cell A2). i tried to do it using BAHTTEXT but i get a
nonreadable text.
 
Costa,

Put the functions below into a standard codemodule - watch line wrapping errors - and use it by entering

=MakeWords(12345)

or

=MakeWords(A2)

where A2 contains 12345

HTH,
Bernie

Function MakeWords(ByVal InValue As Double) As String

MakeWords = ""

n = InValue

trill = n / 1000000000000#
If Int(trill) > 0 Then
MakeWords = MakeWord(Int(trill)) & " trillion "
End If

n = n - Int(trill) * 1000000000000#
bill = n / 1000000000
If Int(bill) > 0 Then
MakeWords = MakeWords & MakeWord(Int(bill)) & " billion "
End If

n = n - Int(bill) * 1000000000
mill = n / 1000000
If Int(mill) > 0 Then
MakeWords = MakeWords & MakeWord(Int(mill)) & " million "
End If

n = n - Int(mill) * 1000000
thou = n / 1000
If Int(thou) > 0 Then
MakeWords = MakeWords & MakeWord(Int(thou)) & " thousand "

End If

n = n - Int(thou) * 1000
If n > 0 Then
MakeWords = MakeWords & MakeWord(Int(n))
End If

MakeWords = Application.WorksheetFunction.Proper(Trim(MakeWords))
End Function

Function MakeWord(InValue As Integer) As String
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"
End If
hund = n \ 100
If hund > 0 Then
MakeWord = MakeWord & MakeWord(Int(hund)) & " hundred "
End If
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.WorksheetFunction.Proper(Trim(MakeWord))
End Function
 
Back
Top