Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Currency Convert Formula

Posted on 1998-11-30
Medium Priority
782 Views
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
Question by:bakery
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 2

Expert Comment

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

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.

Thanks
0

LVL 2

Expert Comment

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

'==========================================================
'     ======
' 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

Function round_curr(currValue)

'     '
'     'This rounds any currency field
'     '

round_curr = currValue
End Function

0

Author Comment

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 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

Thanks once again for yr excellent answer
0

LVL 7

Accepted Solution

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
Dim intTemp As Integer, strTemp  As String

say_triada = Choose(Left(triada, 1) + 1, "", "One ", "Two ", "Three ", "Four ", _
"Five ", "Six ", "Seven ", "Eight ", "Nine ")

If Right(triada, 2) > 19 Then
Choose(Mid(triada, 2, 1) + 1, "", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", _
"Sixty ", "Seventy ", "Eighty ", "Ninety ")
Else
End If

"", "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

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

ID: 1969594

Sir Dedushka's

shekar

0

LVL 7

Expert Comment

ID: 1969595
Shekar, you welcome!

khamed@istnet.ru

Dedushka
0

## Featured Post

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …
###### Suggested Courses
Course of the Month6 days, 5 hours left to enroll