If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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) |
#2
|
|||
|
|||
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) |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
convert number to text and format it. | Goda | Worksheet Functions | 2 | February 7th, 2005 08:07 PM |
Convert text numbers to number format | montagu | General Discussion | 1 | November 18th, 2004 03:59 PM |
Convert a number to text and format it | Dave | Running & Setting Up Queries | 4 | September 3rd, 2004 06:29 PM |
email format | Lloyd | General Discussion | 1 | June 16th, 2004 03:42 AM |
how to convert the number into its text format | pankaj | Worksheet Functions | 2 | November 22nd, 2003 02:57 PM |