Convert a numeric value into English words in Microsoft Excel

[Bavol]

Hello friends,

I have created an excel file in Office 2007 where i need to convert some numeric number into English words and this is very important for me. i have try to find a lot over internet but was not able to get any solution for it. When i asked my friend about it he told me that you can convert numeric number into English words but for that you need to use some codes for it and he don't have nay idea about it. Can anyone help me out with this issue ?

[MindSpace]

For converting a numeric value into English words in Microsoft Excel you need to first Open Microsoft Excel and then press ALT+F11 to start the Visual Basic Editor over there you will find out Insert menu and module too now click on Module and then try the following code in the module sheet.

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Rupees, Paise, Temp
    Dim Decimal_Place, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    ' String representation of amount.
    My_Number = Trim(Str(My_Number))
    ' Position of decimal place 0 if none.
    Decimal_Place = InStr(My_Number, ".")
    ' Convert Paise and set My_Number to dollar amount.
    If Decimal_Place > 0 Then
        Paise = GetTens(Left(Mid(My_Number, Decimal_Place + 1) & _
                  "00", 2))
        My_Number = Trim(Left(My_Number, Decimal_Place - 1))
    End If
    Count = 1
    Do While My_Number <> ""
        Temp = GetHundreds(Right(My_Number, 3))
        If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
     End If
        Count = Count + 1
    Loop
    Select Case Rupees
        Case ""
            Rupees = "No Rupees"
        Case "One"
            Rupees = "One Rupees"
         Case Else
            Rupees = Rupees & “Rupees"
    End Select
    Select Case Paise
        Case ""
            Paise = " and No Paise"
        Case "One"
            Paise = " and One Paise"
              Case Else
            Paise = " and " & Paise & " Paise"
    End Select
    SpellNumber = Rupees & Paise
End Function
      
' Converts a number from 100-999 into text 
Function GetHundreds(ByVal My_Number)
    Dim Result As String
    If Val(My_Number) = 0 Then Exit Function
    My_Number = Right("000" & My_Number, 3)
    ' Convert the hundreds place.
    If Mid(My_Number, 1, 1) <> "0" Then
        Result = GetDigit(Mid(My_Number, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(My_Number, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(My_Number, 2))
    Else
        Result = Result & GetDigit(Mid(My_Number, 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

[Bavol]

Thanks for replying me I have try to use the above code which you have provide, but when i am trying to use it i am getting some kind of error message, can anyone provide me a proper code by using which i can Convert a numeric value into English words in Microsoft Excel.

[Modifier]

According to me MindSpace has provided you proper code but he has miss out if condition, don't worry about it try to use the below i have made correction for the same and now you can Convert a numeric value into English words if you want to refer to other cells in the workbook then you can use Cell reference and type the following formula into another cell:

=SpellNumber(A1)

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Rupees, Paise, Temp
    Dim Decimal_Place, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    ' String representation of amount.
    My_Number = Trim(Str(My_Number))
    ' Position of decimal place 0 if none.
    Decimal_Place = InStr(My_Number, ".")
    ' Convert Paise and set My_Number to dollar amount.
    If Decimal_Place > 0 Then
        Paise = GetTens(Left(Mid(My_Number, Decimal_Place + 1) & _
                  "00", 2))
        My_Number = Trim(Left(My_Number, Decimal_Place - 1))
    End If
    Count = 1
    Do While My_Number <> ""
        Temp = GetHundreds(Right(My_Number, 3))
        If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
        If Len(MyNumber) > 3 Then
            My_Number = Left(My_Number, Len(My_Number) - 3)
        Else
            My_Number = ""
        End If
        Count = Count + 1
    Loop
    Select Case Rupees
        Case ""
            Rupees = "No Rupees"
        Case "One"
            Rupees = "One Rupees"
         Case Else
            Rupees = Rupees & “Rupees"
    End Select
    Select Case Paise
        Case ""
            Paise = " and No Paise"
        Case "One"
            Paise = " and One Paise"
              Case Else
            Paise = " and " & Paise & " Paise"
    End Select
    SpellNumber = Rupees & Paise
End Function
      
' Converts a number from 100-999 into text 
Function GetHundreds(ByVal My_Number)
    Dim Result As String
    If Val(My_Number) = 0 Then Exit Function
    My_Number = Right("000" & My_Number, 3)
    ' Convert the hundreds place.
    If Mid(My_Number, 1, 1) <> "0" Then
        Result = GetDigit(Mid(My_Number, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(My_Number, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(My_Number, 2))
    Else
        Result = Result & GetDigit(Mid(My_Number, 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

[ashpu]

what to do after entering code in the module ....
straightway exit or what ? please guide me