Formula to convert numaric to word function in excel

'****************

    ' Main Function *

 '****************

 

      Function SpellRupee(ByVal MyNumber)

          Dim Dollars, Cents, Temp, Temp1, Dollars1, Temp2

          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

Temp2 = ""

If Len(MyNumber) > 7 Then

         Temp2 = GetHundreds(Left(MyNumber, Len(MyNumber) - 7)) & " CORES "

MyNumber = Right(MyNumber, 7)

 

End If

 

         Temp1 = ""

         If Len(MyNumber) > 5 Then

         Temp1 = GetHundreds(Left(MyNumber, Len(MyNumber) - 5)) & " LAKHS "

MyNumber = Right(MyNumber, 5)

 

          End If

 

          Do While MyNumber <> ""

              Temp = GetHundreds(Right(MyNumber, 3))

              If Temp <> "" Then Dollars1 = Temp & Place(Count) & Dollars1

              If Len(MyNumber) > 3 Then

                  MyNumber = Left(MyNumber, Len(MyNumber) - 3)

              Else

                  MyNumber = ""

              End If

              Count = Count + 1

          Loop

          Dollars = Temp2 & Temp1 & Dollars1

          Select Case Dollars

              Case ""

                  Dollars = "NIL RUPEES"

              Case "One"

                  Dollars = "ONE RUPEE "

              Case Else

                  Dollars = " RUPEES " & Dollars & " ONLY "

          End Select

 

          Select Case Cents

              Case ""

                  Cents = " "

              Case "One"

                  Cents = " "

              Case Else

                  Cents = " AND " & Cents & " PAISA"

          End Select

 

          SpellRupee = Dollars & Cents

      End Function

 

 

 

      '*******************************************

      ' Converts a number from 100-999 into text *

      '*******************************************

 

     Private 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 = GetNumber(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 & GetNumber(Mid(MyNumber, 3))

          End If

 

          GetHundreds = Result

      End Function

 

 

 

      '*********************************************

      ' Converts a number from 10 to 99 into text. *

      '*********************************************

 

     Private 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 & GetNumber _

                  (Right(TensText, 1))  ' Retrieve ones place.

          End If

          GetTens = Result

      End Function

 

 

 

 

      '*******************************************

      ' Converts a number from 1 to 9 into text. *

      '*******************************************

 

     Private Function GetNumber(Digit)

          Select Case Val(Digit)

              Case 1: GetNumber = "ONE"

              Case 2: GetNumber = "TWO"

              Case 3: GetNumber = "THREE"

              Case 4: GetNumber = "FOUR"

              Case 5: GetNumber = "FIVE"

              Case 6: GetNumber = "SIX"

              Case 7: GetNumber = "SEVEN"

              Case 8: GetNumber = "EIGHT"

              Case 9: GetNumber = "NINE"

              Case Else: GetNumber = ""

          End Select

      End Function

 

 

 

 

 

 

 

 _______________________________________________

Paste the same at VBE moudle

 

 

 

Replies (2)

Hi Rajesh!

How can one use these formulas in a effective manner ?

Please explain.

Open excel

go to Macro

click at  Visual Basic Editor

click on file

open new module

paste this function in that

sale the module

now you can use this function as excel formula

type  "=spellrupee(C5)"

 

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Company
10 November 2026
FP&A Executive

Gujarat Guardian

Ankleshwar

CA Final

View Details
Company
ARTICLESHIP 16 November 2026
Audit Executives

Loonker Godawat And Company

Bengaluru

B.Com

View Details
Company
20 November 2026
Charted Accountant

Cothas Coffee Co.

Bengaluru

CA

View Details
Company
Featured 29 October 2026
Accounts Executive

Synergy Keystone

Chennai

B.Com

View Details
Company
31 October 2026
Accountants

sankaran and krishnan

New Delhi

B.Com

View Details
Company
ARTICLESHIP 14 November 2026
Article Assistant

Jitendra Agarwal & Associates

Bengaluru

CA Inter

View Details
Company
16 November 2026
Indirect Tax Associate

Sekhri Kanodia & Associates

Mumbai

B.Com

View Details
Company
ARTICLESHIP 25 October 2026
Article Intern (CA Articleship)

Arpan Bohra & Co

Hyderabad

CA Inter

View Details