How To Convert Number with Your Currency in Excel like (10 into Ten) in Excel 2007 , 2010

0

now i am going to tech you how to convert Number in words with your currency in Excel. Excel Does not have a default function that you can change numbers in English words in a worksheet, but you can change numbers in English words by using SpellNumber function code into a VBA (visual Basic for Applications) module. this is very useful function so please follow these steps

How To Convert Number with Your Currency in Excel like (10 into Ten) in Excel 2007 , 2010
  1. Copy Following Code
REM  *****  BASIC  *****
‘Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = “Thousand “
    Place(3) = “Million “
    Place(4) = “Billion “
    Place(5) = “Trillion “
    ‘ String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ‘ Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, “.”)
    ‘ Convert cents and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  “00”, 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace – 1))
    End If
    Count = 1
    Do While MyNumber <> “”
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> “” Then Dollars = Temp & Place(Count) & Dollars
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) – 3)
        Else
            MyNumber = “”
        End If
        Count = Count + 1
    Loop
    Select Case Dollars
        Case “”
            Dollars = Str(Dollars)
        Case “One “
            Dollars = “One Dollar “
         Case Else
            Dollars = Dollars & ” Dollars “
    End Select
        If Dollars = “” Then
         Select Case Cents
        Case “”
            Cents = “”
        Case “One “
            Cents = “One Cent Only”
              Case Else
            Cents = Cents & “Cents Only”
    End Select
        Else
        Select Case Cents
        Case “”
            Cents = “Only”
        Case “One “
            Cents = “and One Cent Only”
              Case Else
            Cents = “and ” & Cents & “Cents Only”
    End Select
        End If
    SpellNumber = Dollars & Cents
End Function
   
‘ Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right(“000” & MyNumber, 3)
    ‘ Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> “0” Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & “Hundred “
    End If
    ‘ Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> “0” Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function
   
‘ Converts a number from 10 to 99 into text.
Function GetTens(TensText)
    Dim Result As String
    Result = “”           ‘ Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ‘ If value between 10-19…
        Select Case Val(TensText)
            Case 10: Result = “Ten “
            Case 11: Result = “Eleven “
            Case 12: Result = “Twelve “
            Case 13: Result = “Thirteen “
            Case 14: Result = “Fourteen “
            Case 15: Result = “Fifteen “
            Case 16: Result = “Sixteen “
            Case 17: Result = “Seventeen “
            Case 18: Result = “Eighteen “
            Case 19: Result = “Nineteen “
            Case Else
        End Select
    Else                                 ‘ If value between 20-99…
        Select Case Val(Left(TensText, 1))
            Case 2: Result = “Twenty “
            Case 3: Result = “Thirty “
            Case 4: Result = “Forty “
            Case 5: Result = “Fifty “
            Case 6: Result = “Sixty “
            Case 7: Result = “Seventy “
            Case 8: Result = “Eighty “
            Case 9: Result = “Ninety “
            Case Else
        End Select
        Result = Result & GetDigit _
            (Right(TensText, 1))  ‘ Retrieve ones place.
    End If
    GetTens = Result
End Function
   
‘ Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = “One “
        Case 2: GetDigit = “Two “
        Case 3: GetDigit = “Three “
        Case 4: GetDigit = “Four “
        Case 5: GetDigit = “Five “
        Case 6: GetDigit = “Six “
        Case 7: GetDigit = “Seven “
        Case 8: GetDigit = “Eight “
        Case 9: GetDigit = “Nine “
        Case Else: GetDigit = “”
    End Select
End Function

 

2.Open Excel and Press Alt + F11 Key or Go To Developer tab and click Visual Basic

 

 

 

3. Go to Insert tab and click on Module 

 

4. Past Here VBA Code in VBA Module

 
NOTE :-When you Past VBA code into VBA module so you can see only Dollars and Cents Currency Like 1.50 into English words one Dollars and fifty cents Only so how can you change Dollars and Cents Currency into your Currency 
 

Now How to Change Currency Dollars To Your Currency

1. first copy the VBA Code After that Open the Notepad and past VBA Code and Press Ctrl+H on keyboard and type Dollars into find what box and type your currency into Replace with box after that click Replace all please don’t copy this code yet because we can not change sub currency with our sub currency like cents into paisa 

 

2. Now you change Sub Currency like cents to paisa. so Press one more time Ctrl+H and on keyboard and type cents into find what box and type your currency into Replace with box after that click Replace all 

 

3. After that you can copy this code and past it inVBA module worksheet

 

4. Now type formula =SpellNumber(select number cell where you type amount) 

  5. Now finish this topic

 

thank you for give us your time please give us your feedback thank you

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here