convert number to text and format it.
 

Hi,
I need to convert numbers into text e.i. 150 - one hundred fifty, and I
would like to know how to format it. If I need the numbers to be written in a
different language (e.i.Polish)

Goda,

To do the Polish language version, you will need to replace every English
word in double quotes with the proper Polish. Additionally, you may need to
change the logic, if there are Polish-specific numeric naming conventions
that are different than English .

Copy the code below and paste it into a codemodule, then use it by inserting
a formula in a cell

=Conversion(150)

or

=Conversion(A1)

where A1 has 150 in it.

HTH,
Bernie
MS Excel MVP


Function Conversion(ByVal InValue As Double) As String

Conversion = ""
n = InValue

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

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

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

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

End If

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

Conversion = Application.WorksheetFunction.Proper(Trim(Conversi on))
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


"Goda" wrote in message
...
Hi,
I need to convert numbers into text e.i. 150 - one hundred fifty, and I
would like to know how to format it. If I need the numbers to be written
in a
different language (e.i.Polish)

A German example you can find at:
http://www.bplumhoff.de/html/inworten.html

But keep in mind as Bernie Deitrick told you: The logic
of "wording" the numbers has to be similar...

Kind regards,
Bernd