NUMBER TO WORDS IN EXCEL

 

    NUMBER TO WORDS IN EXCEL

 

In Excel on  most of the Windows  there is not a ready  built-in function to convert numbers to words (like "123" to "One Hundred Twenty-Three"). However, you can do this using  the methods below:----------------------------------------------

1. Using VBA, (Visual Basic for Applications) (Macro) Code.

Steps to  insert VBA code :

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the VBA editor, click Insert in the menu and then choose Module.
3. In the module window, paste the  VBA code which is appended below.
4. After pasting the code, press Ctrl + S to save your workbook. Make sure to save it as a Macro-enabled Workbook (.xlsm).
5. Close the VBA editor (Alt + Q) and go back to your Excel worksheet.

Using the Function in Excel:

Now, you can use the new function NumToWords in any cell. For example, if you want to convert the number in cell A1 to words, use the following formula:

=NumToWords(A1)

  

=NUMTOWORDS(A1)

VBA Code to copy and paste ------

Function NumToWords(ByVal MyNumber As Double) As String

    Dim Units As String

    Dim TempStr As String

    Dim DecimalSeparator As String

    Dim DecimalPart As String

    Dim DecimalWords As String

    Dim i As Integer

   

    ' Define the decimal separator (use "." for English locale)

    DecimalSeparator = "."

   

    ' Exit if MyNumber is zero

    If MyNumber = 0 Then

        NumToWords = "Zero"

        Exit Function

    End If

   

    ' Split the number into whole number part and decimal part

    If InStr(CStr(MyNumber), DecimalSeparator) > 0 Then

        TempStr = Left(CStr(MyNumber), InStr(CStr(MyNumber), DecimalSeparator) - 1) ' Whole part

        DecimalPart = Mid(CStr(MyNumber), InStr(CStr(MyNumber), DecimalSeparator) + 1) ' Decimal part

    Else

        TempStr = CStr(MyNumber)

        DecimalPart = ""

    End If

   

    ' Convert the whole number part to words

    Units = GetUnits(TempStr)

   

    ' Convert the decimal part into words, digit by digit

    If DecimalPart <> "" Then

        DecimalWords = "Point"

        For i = 1 To Len(DecimalPart)

            DecimalWords = DecimalWords & " " & GetUnits(Mid(DecimalPart, i, 1))

        Next i

        NumToWords = Units & " " & DecimalWords

    Else

        ' If no decimal part, just return the whole number in words

        NumToWords = Units

    End If

End Function

 

Private Function GetUnits(ByVal MyNumber As String) As String

    Dim UnitsArr As Variant

    Dim TempStr As String

    Dim Result As String

    Dim i As Integer

   

    UnitsArr = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", _

                     "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", _

                     "Eighteen", "Nineteen", "Twenty", "Twenty One", "Twenty Two", "Twenty Three", _

                     "Twenty Four", "Twenty Five", "Twenty Six", "Twenty Seven", "Twenty Eight", _

                     "Twenty Nine", "Thirty", "Thirty One", "Thirty Two", "Thirty Three", _

                     "Thirty Four", "Thirty Five", "Thirty Six", "Thirty Seven", "Thirty Eight", _

                     "Thirty Nine", "Forty", "Forty One", "Forty Two", "Forty Three", "Forty Four", _

                     "Forty Five", "Forty Six", "Forty Seven", "Forty Eight", "Forty Nine", _

                     "Fifty", "Fifty One", "Fifty Two", "Fifty Three", "Fifty Four", "Fifty Five", _

                     "Fifty Six", "Fifty Seven", "Fifty Eight", "Fifty Nine", "Sixty", "Sixty One", _

                     "Sixty Two", "Sixty Three", "Sixty Four", "Sixty Five", "Sixty Six", "Sixty Seven", _

                     "Sixty Eight", "Sixty Nine", "Seventy", "Seventy One", "Seventy Two", "Seventy Three", _

                     "Seventy Four", "Seventy Five", "Seventy Six", "Seventy Seven", "Seventy Eight", _

                     "Seventy Nine", "Eighty", "Eighty One", "Eighty Two", "Eighty Three", "Eighty Four", _

                     "Eighty Five", "Eighty Six", "Eighty Seven", "Eighty Eight", "Eighty Nine", _

                     "Ninety", "Ninety One", "Ninety Two", "Ninety Three", "Ninety Four", "Ninety Five", _

                     "Ninety Six", "Ninety Seven", "Ninety Eight", "Ninety Nine")

   

    TempStr = MyNumber

    If TempStr > 99 Then

        Result = UnitsArr(Int(TempStr / 100)) & " Hundred"

        TempStr = TempStr Mod 100

    End If

   

    If TempStr > 19 Then

        Result = Result & " " & UnitsArr(Int(TempStr / 10) + 18)

        TempStr = TempStr Mod 10

    End If

   

    If TempStr > 0 Then

        Result = Result & " " & UnitsArr(TempStr)

    End If

   

    GetUnits = Trim(Result)

End Function