Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Currency Convert Formula

Posted on 1998-11-30
8
Medium Priority
?
796 Views
Last Modified: 2010-08-05
I have total amount in kuwaiti dinar 12.344, this amount in numeric word and text.

In my invoice 12.344 total amount.

In invoice it must be in text like this (kuwaiti dinar twele and three hundred forty four only)

pls help to get this formula

Thanks
Shekar
0
Comment
Question by:bakery
8 Comments
 
LVL 2

Expert Comment

by:richtsteig
ID: 1969588
Look at article ID

Q95640

of the Microsoft Knowledge Base (support.microsoft.com).
You will find the sourcecode of a function to convert a number into currency (english words)
0
 

Author Comment

by:bakery
ID: 1969589
Unsatisfied with your answer, please send me exact formula for my change of currency in text .
I checked in support/microsoft.com but failed to get this answer.

pls this is very immportent to me to get from u only. Pls explain me the formula.

appreciate your reply

Thanks
0
 
LVL 2

Expert Comment

by:dm_14
ID: 1969590
from
United Arab Emirates

Here is the code for the conversion :


Example : NumToWord (23.343)
KUWAITI DINAR Twenty Three and FILS 343 only


'===========================================================
'     =====
'     '*** This is the main function call
'===========================================================
'     =====

Function NumToWord(numval)

        Dim NTW, NText, dollars, cents, NWord, totalcents As String
        Dim decplace, TotalSets, cnt, LDollHold As Integer
        ReDim NumParts(9) As String 'Array for Amount (sets of three)
        ReDim Place(9) As String 'Array containing place holders
        Dim LDoll As Integer 'Length of the Dollars Text Amount
        Place(2) = " Thousand " '
        Place(3) = " Million " 'Place holder names for money
        Place(4) = " Billion " 'amounts
        Place(5) = " Trillion " '
        NTW = "" 'Temp value for the function
        NText = round_curr(numval) 'Roundup the cents to eliminate       cents gr 2
        NText = Trim(Str(NText)) 'String representation of amount
        decplace = InStr(Trim(NText), ".") 'Position of decimal 0 if none
        dollars = Trim(Left(NText, IIf(decplace = 0, Len(numval), decplace - 1)))
        LDoll = Len(dollars)
        cents = Trim(Right(NText, IIf(decplace = 0, 0, Abs(decplace - Len(NText)))))
        If Len(cents) = 1 Then
        cents = cents & "0"
        End If
        If (LDoll Mod 3) = 0 Then
        TotalSets = (LDoll \ 3)
        Else
        TotalSets = (LDoll \ 3) + 1
        End If
        cnt = 1
        LDollHold = LDoll
        Do While LDoll > 0
        NumParts(cnt) = IIf(LDoll > 3, Right(dollars, 3), Trim(dollars))
        dollars = IIf(LDoll > 3, Left(dollars, (IIf(LDoll < 3, 3, LDoll)) - 3), "")
        LDoll = Len(dollars)
        cnt = cnt + 1
        Loop
        For cnt = TotalSets To 1 Step -1 'step through NumParts array
        NWord = GetWord(NumParts(cnt)) 'convert 1 element of       NumParts
        iP1 = InStr(cnt, ".")
        NTW = NTW & NWord 'concatenate it to temp       variable
        If NWord <> "" Then NTW = NTW & Place(cnt)
        Next cnt 'loop through
        If LDollHold > 0 Then
        NTW = "KUWAITI DINAR " & NTW & " and " 'concatenate text
        Else
        NTW = " NO KUWAITI DINAR " & NTW & " and " 'concatenate text
        End If
        If Len(cents) > 3 Then
            cents = Left(cents, 3)
        End If
        totalcents = cents 'GetTens(cents) 'Convert cents part to word
        If totalcents = "" Then totalfils = "NO" 'Concat NO if cents=0
        NTW = NTW & "FILS " & totalcents & " only" 'Concat Dollars and Cents
        NumToWord = NTW 'Assign word value to


End Function

 '==========================================================
'     ======
 ' The following function converts a number from 1 to 9 to t
'     ext
 '==========================================================
'     ======

Function GetDigit(Digit)

        Select Case Val(Digit)
        Case 1: GetDigit = "One" '
        Case 2: GetDigit = "Two" '
        Case 3: GetDigit = "Three" '
        Case 4: GetDigit = "Four" ' Assign a numeric word value
        Case 5: GetDigit = "Five" ' based on a single digit.
        Case 6: GetDigit = "Six" '
        Case 7: GetDigit = "Seven" '
        Case 8: GetDigit = "Eight" '
        Case 9: GetDigit = "Nine" '
        Case Else: GetDigit = "" '
        End Select
End Function 'End function GetDigit - return to calling program

 '==========================================================
'     ======
 ' The following function converts a number from 10 to 99 to
'      text
 '==========================================================
'     ======

Function GetTens(tenstext)

        Dim GT As String
        GT = "" 'null out the temporary function value
        If Val(Left(tenstext, 1)) = 1 Then ' If value between 10-19
        Select Case Val(tenstext)
       Case 10: GT = "Ten" '
       Case 11: GT = "Eleven" '
       Case 12: GT = "Twelve" '
       Case 13: GT = "Thirteen" ' Retrieve numeric word
       Case 14: GT = "Fourteen" ' value if between ten and
       Case 15: GT = "Fifteen" ' nineteen inclusive.
       Case 16: GT = "Sixteen" '
       Case 17: GT = "Seventeen" '
       Case 18: GT = "Eighteen" '
       Case 19: GT = "Nineteen" '
       Case Else
        End Select
         
        Else ' If value between 20-99
        Select Case Val(Left(tenstext, 1))
         
       Case 2: GT = "Twenty " '
       Case 3: GT = "Thirty " '
       Case 4: GT = "Forty " '
       Case 5: GT = "Fifty " ' Retrieve value if it is
       Case 6: GT = "Sixty " ' divisible by ten
       Case 7: GT = "Seventy " ' excluding the value ten.
       Case 8: GT = "Eighty " '
       Case 9: GT = "Ninety " '
       Case Else
        End Select
        GT = GT & GetDigit(Right(tenstext, 1)) 'Retrieve ones place
        End If
         
        GetTens = GT ' Assign function return value.
End Function


'===========================================================
'     ======
' The following function converts a number from 0 to 999 to
'     text
'===========================================================
'     ======

Function GetWord(NumText)

        Dim GW As String, x As Integer
        GW = "" 'null out temporary function value
        If Val(NumText) > 0 Then
        For x = 1 To Len(NumText) 'loop the length of NumText times
       Select Case Len(NumText)
       Case 3:
        If Val(NumText) > 99 Then
        GW = GetDigit(Left(NumText, 1)) & " Hundred " 
        End If
        NumText = Right(NumText, 2)
       Case 2:
        GW = GW & GetTens(NumText)
        NumText = "" 
       Case 1:
        GW = GetDigit(NumText)
       Case Else
End Select

 Next x
 End If
 GetWord = GW 'assign function return value
End Function 'End function GetWord - Return to calling program


Function round_curr(currValue)

       '     '
       '     'This rounds any currency field
       '     '
       
       round_curr = currValue
End Function

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:bakery
ID: 1969591
dm_14

Excellent and successfull amount in hundreds only
if the amount more than like 2,345.790 (two thousand three hundred forty five and fils 789)
this amount will not read, it is read only three hundred forty five and fils 789.

Why not reading thousand?
Why the amount reading less 1 fils
Why the amount if fils in last 500 it is reading only 5  but the 00 will not showing in print

pls modify and answer me
Thanks once again for yr excellent answer
0
 
LVL 7

Accepted Solution

by:
Dedushka earned 200 total points
ID: 1969592
Hi, bakery!

The translation of moneys in words from my point of view requires no more than 50 lines of a code (VBA MSA97).

----------
Function say(curAMOUNT As Currency) As String
Dim intTmp As Integer, strAmount As String, strKop As String

strAmount = Format$(curAMOUNT, "#0.000")
strKop = Right(strAmount, 3)
If strKop = "000" Then
strKop = "only"
Else
strKop = "and FILS " & strKop & " only"
End If

strAmount = Left(strAmount, Len(strAmount) - 4)
If strAmount = "0" Then
    say = "NO KUWAITI DINAR "
Else
    say = "KUWAITI DINAR "
    Select Case Len(strAmount) Mod 3
        Case 1:    strAmount = "00" & strAmount
        Case 2:    strAmount = "0" & strAmount
    End Select
    For intTmp = Len(strAmount) \ 3 To 1 Step -1
        say = say & say_triada(Mid(strAmount, Len(strAmount) - 3 * intTmp + 1, 3), intTmp)
    Next
End If
say = say & strKop
End Function
Function say_triada(ByVal triada As String, ByVal triada_no As Integer) As String
Dim intTemp As Integer, strTemp  As String

If triada = "000" And triada_no > 1 Then Exit Function

say_triada = Choose(Left(triada, 1) + 1, "", "One ", "Two ", "Three ", "Four ", _
           "Five ", "Six ", "Seven ", "Eight ", "Nine ")
If say_triada <> "" Then say_triada = say_triada & "Houndred "

If Right(triada, 2) > 19 Then
    intTemp = Right(triada, 1)
    say_triada = say_triada & _
    Choose(Mid(triada, 2, 1) + 1, "", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", _
                                "Sixty ", "Seventy ", "Eighty ", "Ninety ")
Else
    intTemp = Right(triada, 2)
End If
   
say_triada = say_triada & Choose(intTemp + 1, _
            "", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", _
            "Eight ", "Nine ", "Ten ", "Eleven ", "Twelve ", _
            "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", _
            "Seventeen ", "Eighteen ", "Nineteen ") _
            & Choose(triada_no, "", "Thousand", "Million", "Billion", "Trillion")
End Function
---------
Cheers!
Dedushka
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1969593
Sorry, bakery.

Change this:

& Choose(triada_no, "", "Thousand", "Million", "Billion", "Trillion")

to

& Choose(triada_no, "", "Thousand ", "Million ", "Billion ", "Trillion ")

Dedushka
0
 

Author Comment

by:bakery
ID: 1969594

Sir Dedushka's

Your currency conver answer is realy very Excellent, it works without any problem, I thank u once again and can I have your email address if any other help i need from u Pls.

shekar

0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1969595
Shekar, you welcome!

khamed@istnet.ru

Dedushka
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question