Convert currency into words

[jocer]

Good Morning guys,

I show one macro with the above subject and it is absolute i need but i need to put the currency word beside (red color) the number like below
123.5 = One Hundred Twenty Three Dirhams and 50/100 fils.
.50 = 50/100 fils.

the previous macro i saw is 123.5 = one hundred twenty three and 50/100 only without the currency word.

Can you help me please

thanks in advance

[:) Sixthsense :)]

I suggest you to make use of the NB.TEXT Function of Morefunc addin which is more user friendly.

You can get the Morefunc Addin by doing a google search.

[mc84excel]

Try the function at bottom of this post.
Call function as follows:

Msgbox NumsToWords(123.50,"Dirham","fil")

It works for me.

Option Explicit
' Downloaded from www.contextures.com
'*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
'* NumsToWords(NumSource, MajorCurrency, MinorCurrency, MajorMinorLink) function                     *
'*                                                                                                   *
'* Where:Words                                                                                       *
'* NumSource:      Number, or cell reference containing the number, to be converted to words         *
'* MajorCurrency:  Primary currency name.......................... (Optional: Default is "Dollar")   *
'* MinorCurrency:  Secondary currency name........................ (Optional: Default is "Cent")     *
'* MajorMinorLink: Word to connect Major and Minor Currency....... (Optional: Default is "and")      *
'* SkipMinor:      True/False flag to ignore the MinorCurrency.... (Optional: Default is FALSE)      *
'*                                                                                                   *
'* Programmer:    Ron Coderre                                                                           *
'* Created on:    14-JUL-2007                                                                           *
'* Last Modified: 24-MAR-2009                                                                           *
'*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*


Public Function NumsToWords( _
    NumSource As Currency, _
    Optional MajorCurrency As String = "Dollar", _
    Optional MinorCurrency As String = "Cent", _
    Optional MajorMinorLink As String = "and", _
    Optional SkipMinor As Boolean = False _
    ) As String
  
    Dim Words As String       ' Used to build the word phrase
    Dim WIPnum As String      ' Orig number formatted as 000000000000000.00
    Dim LU_NumList()          ' Array of numbers to match during the process
    Dim LU_NumText()          ' Text values associated with LU_NumList values
    Dim iMisc As Integer      ' Container for interim calculations
    Dim iCtr As Integer       ' Counter variable
    Dim LU_Denom()            ' Array of groups (Trillion, Billion, etc)
    Dim DecSepChar            ' Decimal separator symbol ( eg English: . )
    LU_NumList = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, _
                      11, 12, 13, 14, 15, 16, 17, 18, 19, _
                      20, 30, 40, 50, 60, 70, 80, 90)
    
    LU_NumText = Array("", " One", " Two", " Three", " Four", " Five", _
       " Six", " Seven", " Eight", " Nine", " Ten", " Eleven", _
       " Twelve", " Thirteen", " Fourteen", " Fifteen", " Sixteen", _
       " Seventeen", " Eighteen", " Nineteen", " Twenty", " Thirty", _
       " Forty", " Fifty", " Sixty", " Seventy", " Eighty", " Ninety")
    
    DecSepChar = Application.International(xlDecimalSeparator)
    
    LU_Denom = Array(" Trillion", " Billion", " Million", " Thousand", "", "")
    
    WIPnum = Replace(Format(Abs(NumSource), "000000000000000.00;KillFlow"), DecSepChar, "0")
    
    'Pull successive WIPnum triads and assign word values
    For iCtr = 0 To 5
       iMisc = CInt(Mid(WIPnum, (1 + iCtr * 3), 3))
       
       If Int(iMisc / 100) > 0 Then Words = Words & LU_NumText(Int(iMisc / 100)) & " Hundred"
       
       'Set the tens and ones phrase
       If (iMisc Mod 100) > 19 Then
          Words = Words & LU_NumText(Int((iMisc Mod 100) / 10) + 18) & LU_NumText(iMisc Mod 10)
       Else
          Words = Words & LU_NumText(iMisc Mod 100)
       End If
       
       If iMisc > 0 Then Words = Words & LU_Denom(iCtr)
       
       If iCtr = 4 Then  ' Finish building the whole nums phrase
          Words = Words & " " & MajorCurrency
          If Int(NumSource) = 0 Then Words = "No" & Words
          If Int(NumSource) <> 1 And MajorCurrency <> "" Then Words = Words & "s"
          If SkipMinor = False Then Words = Words & " " & MajorMinorLink Else Exit For
       
       ElseIf iCtr = 5 Then 'Complete the MinorCurrency phrase
          If SkipMinor = False Then
             If iMisc = 0 Then Words = Words & " No"
             Words = Words & " " & MinorCurrency
             If iMisc <> 1 And MinorCurrency <> "" Then Words = Words & "s"
          End If
       End If
    Next iCtr
    
    NumsToWords = Trim(Replace(Words, "  ", " "))
End Function

[jocer]

thanks mate it is working but i need the fraction not words like 123.50 will be One Hundred Twenty Three Dirhams and 50/100 fills.
sorry for the trouble

thanks again for the help

[mc84excel]

This is a bit rough but it should work

Option Explicit
' Downloaded from www.contextures.com
'*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
'* NumsToWords(NumSource, MajorCurrency, MinorCurrency, MajorMinorLink) function                     *
'*                                                                                                   *
'* Where:Words                                                                                       *
'* NumSource:      Number, or cell reference containing the number, to be converted to words         *
'* MajorCurrency:  Primary currency name.......................... (Optional: Default is "Dollar")   *
'* MinorCurrency:  Secondary currency name........................ (Optional: Default is "Cent")     *
'* MajorMinorLink: Word to connect Major and Minor Currency....... (Optional: Default is "and")      *
'* SkipMinor:      True/False flag to ignore the MinorCurrency.... (Optional: Default is FALSE)      *
'*                                                                                                   *
'* Programmer:    Ron Coderre                                                                           *
'* Created on:    14-JUL-2007                                                                           *
'* Last Modified: 24-MAR-2009                                                                           *
'*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*


Public Function NumsToWords( _
    NumSource As Currency, _
    Optional MajorCurrency As String = "Dollar", _
    Optional MinorCurrency As String = "Cent", _
    Optional MajorMinorLink As String = "and", _
    Optional SkipMinor As Boolean = False _
    ) As String
  
    Dim Words As String       ' Used to build the word phrase
    Dim WIPnum As String      ' Orig number formatted as 000000000000000.00
    Dim LU_NumList()          ' Array of numbers to match during the process
    Dim LU_NumText()          ' Text values associated with LU_NumList values
    Dim iMisc As Integer      ' Container for interim calculations
    Dim iCtr As Integer       ' Counter variable
    Dim LU_Denom()            ' Array of groups (Trillion, Billion, etc)
    Dim DecSepChar            ' Decimal separator symbol ( eg English: . )
    LU_NumList = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, _
                      11, 12, 13, 14, 15, 16, 17, 18, 19, _
                      20, 30, 40, 50, 60, 70, 80, 90)
    
    LU_NumText = Array("", " One", " Two", " Three", " Four", " Five", _
       " Six", " Seven", " Eight", " Nine", " Ten", " Eleven", _
       " Twelve", " Thirteen", " Fourteen", " Fifteen", " Sixteen", _
       " Seventeen", " Eighteen", " Nineteen", " Twenty", " Thirty", _
       " Forty", " Fifty", " Sixty", " Seventy", " Eighty", " Ninety")
    
    DecSepChar = Application.International(xlDecimalSeparator)
    
    LU_Denom = Array(" Trillion", " Billion", " Million", " Thousand", "", "")
    
    WIPnum = Replace(Format(Abs(NumSource), "000000000000000.00;KillFlow"), DecSepChar, "0")
    
    'Pull successive WIPnum triads and assign word values
    For iCtr = 0 To 5
       iMisc = CInt(Mid(WIPnum, (1 + iCtr * 3), 3))
       
       If Int(iMisc / 100) > 0 Then Words = Words & LU_NumText(Int(iMisc / 100)) & " Hundred"

        If iCtr = 5 Then 'Complete the MinorCurrency phrase
            If SkipMinor = False Then
                If iMisc = 0 Then
                    Words = Words & " No"
                Else
                    Words = Words & " " & iMisc & "/100"
                End If
                Words = Words & " " & MinorCurrency
                If iMisc <> 1 And MinorCurrency <> "" Then Words = Words & "s"
            End If
            Exit For
        End If


       'Set the tens and ones phrase
       If (iMisc Mod 100) > 19 Then
          Words = Words & LU_NumText(Int((iMisc Mod 100) / 10) + 18) & LU_NumText(iMisc Mod 10)
       Else
          Words = Words & LU_NumText(iMisc Mod 100)
       End If
       
       If iMisc > 0 Then Words = Words & LU_Denom(iCtr)
       
       If iCtr = 4 Then  ' Finish building the whole nums phrase
          Words = Words & " " & MajorCurrency
          If Int(NumSource) = 0 Then Words = "No" & Words
          If Int(NumSource) <> 1 And MajorCurrency <> "" Then Words = Words & "s"
          If SkipMinor = True Then Exit For
          Words = Words & " " & MajorMinorLink
        End If
    Next iCtr
    
    NumsToWords = Trim(Replace(Words, "  ", " "))
End Function

[jocer]

thanks mate so splendid..... how do i close this thread

[jocer]

thanks mate ur genius...... how to close this query already solved.

[mc84excel]

thanks mate ur genius...... how to close this query already solved.

Thank you for the compliment but I don't deserve that. There was already existing code out there that did almost what you wanted. All I had to do was edit this code to have the output return in your desired format.

It looks like you have worked out how to solve the query.