Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change numeric value to words
Is there any function (both vba or worksheet) I can use to change numeric
value to words? For example, it can tell 'one thousand and ten' for the number 1010. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change numeric value to words
Check out this web site:-
http://www.ozgrid.com/VBA/ValueToWords.htm "JOHN" ¼¶¼g©ó¶l¥ó ... Is there any function (both vba or worksheet) I can use to change numeric value to words? For example, it can tell 'one thousand and ten' for the number 1010. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change numeric value to words
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change numeric value to words
This is it!
It was in other language, so change and complete code to your language. Option Explicit Dim Idziesiatka Function SLOWNIE(ByVal Numer) Dim temps, tempd, tempj, zlote, grosze, Licznik, grd, grj, MiejsceDz, T_S If Numer = 0 Then SLOWNIE = "zero $ zero " Exit Function End If If Numer 9999999999999.99 Or Numer < 0 Then MsgBox "Function convert corectly only values between 0 to 9999999999999,99" SLOWNIE = "bad range" Exit Function End If Numer = Trim(Str(Numer)) MiejsceDz = InStr(Numer, ".") If MiejsceDz 0 Then grosze = Left(Mid(Numer, MiejsceDz + 1) & "00", 2) grd = Dziesiatki(Right(grosze, 2)) If Idziesiatka < 1 Then grj = Jednostki(Right(grosze, 1)) End If grosze = " " & grd & grj & "gr" Numer = Trim(Left(Numer, MiejsceDz - 1)) Else grosze = " zero gr" End If If Numer < "" Then Licznik = 1 Do While Numer < "" temps = "" tempd = "" tempj = "" temps = Setki(Right("000" & Numer, 3)) tempd = Dziesiatki(Right("00" & Numer, 2)) If Idziesiatka < 1 Then tempj = Jednostki(Right(Numer, 1)) End If Select Case Licznik Case 1: T_S = temps & tempd & tempj Case 2: T_S = temps & tempd & tempj & KTys(Numer) Case 3: T_S = temps & tempd & tempj & KMil(Numer, Licznik) Case 4: T_S = temps & tempd & tempj & KMil(Numer, Licznik) Case 5: T_S = temps & tempd & tempj & KMil(Numer, Licznik) End Select zlote = T_S & zlote If Len(Numer) 3 Then Numer = Left(Numer, Len(Numer) - 3) Licznik = Licznik + 1 Else Numer = "" End If Loop Else zlote = "zero " End If SLOWNIE = zlote & "$" & grosze End Function Public Function KTys(ByVal Numer) Dim tys tys = Val(Right("000" & Numer, 3)) If tys = 0 Then KTys = "" Else tys = Val(Right(Numer, 2)) If tys = 1 Then KTys = "tousend" Else If tys = 12 Or tys = 13 Or tys = 14 Then KTys = "s " Else tys = Val(Right(Numer, 1)) Select Case tys Case 2, 3, 4: KTys = "s " Case Else: KTys = "s " End Select End If End If KTys = "thousand" & KTys End If End Function Public Function KMil(ByVal Numer, L) Dim mil Dim RzadW(5) As String RzadW(3) = "million" RzadW(4) = "milliard" RzadW(5) = "billion" mil = Val(Right("000" & Numer, 3)) If mil = 0 Then KMil = "" Else mil = Val(Right(Numer, 2)) If mil = 1 Then KMil = " " Else If mil = 12 Or mil = 13 Or mil = 14 Then KMil = "s " Else mil = Val(Right(Numer, 1)) Select Case mil Case 2, 3, 4: KMil = "s " Case Else: KMil = "s " End Select End If End If KMil = RzadW(L) & KMil End If End Function Public Function Setki(ByVal Numer) Dim setka, wynik setka = Val(Left(Numer, 1)) Select Case setka Case 1: wynik = "houndred " '100 Case 2: wynik = "two houndred " '200 Case 3: wynik = " houndred " '300 Case 4: wynik = " houndred " '400 Case 5: wynik = " houndred " '500 Case 6: wynik = " houndred " '600 Case 7: wynik = " houndred " '700 Case 8: wynik = " houndred " '800 Case 9: wynik = " houndred " '900 End Select Setki = wynik End Function Public Function Dziesiatki(ByVal Number) Dim wynik Idziesiatka = Val(Left(Number, 1)) If Idziesiatka = 1 Then Select Case Val(Number) Case 10: wynik = "ten " Case 11: wynik = " " Case 12: wynik = " " Case 13: wynik = " " Case 14: wynik = " " Case 15: wynik = " " Case 16: wynik = " " Case 17: wynik = " " Case 18: wynik = " " Case 19: wynik = " " End Select Else Select Case Idziesiatka Case 2: wynik = " " '20 Case 3: wynik = " " '30 Case 4: wynik = " " '40 Case 5: wynik = " " '50 Case 6: wynik = " " '60 Case 7: wynik = " " '70 Case 8: wynik = " " '80 Case 9: wynik = " " '90 End Select End If Dziesiatki = wynik End Function Public Function Jednostki(ByVal Numer) Dim jedst, wynik jedst = Val(Right(Numer, 1)) Select Case jedst Case 1: wynik = "one " Case 2: wynik = "two " Case 3: wynik = " " Case 4: wynik = " " Case 5: wynik = " " Case 6: wynik = " " Case 7: wynik = " " Case 8: wynik = " " Case 9: wynik = " " End Select Jednostki = wynik End Function U¿ytkownik "JOHN" napisa³ w wiadomo¶ci ... Is there any function (both vba or worksheet) I can use to change numeric value to words? For example, it can tell 'one thousand and ten' for the number 1010. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change numeric value to words
Hi John,
Look for "VBA spell numbers function" at this site: http://cpap.com.br/orlando HTH --- (So that you get best and rapid solution and all may benefit from the discussion, please reply within the newsgroup, not in email) "JOHN" escreveu na mensagem ... Is there any function (both vba or worksheet) I can use to change numeric value to words? For example, it can tell 'one thousand and ten' for the number 1010. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a numeric value into words | Excel Discussion (Misc queries) | |||
how to convert numeric to words Example 525 in words (Twenty five | Excel Discussion (Misc queries) | |||
how to convert numeric into words | New Users to Excel | |||
how do I convert numeric data to words? | Excel Discussion (Misc queries) | |||
convert numeric value to words, error msg! | Excel Discussion (Misc queries) |