An excel solution to convert digit into words

[PRADEEPB270]

An Indian currency is 'INR' ( Rupees ).Most often,we want to convert the numeric into words.I want an excel formula who convert the digital or numeric figure into words.See the following example:-

Numeric Words ( Excel Formula Required )
1.00 Rupee One Only
12.45 Rupees Twelve And Paise Forty Five Only
123.00 Rupees One Hundred Twenty Three Only
1,234.89 Rupees One Thousand Two Hundred Thirty Four And Paise Eighty Nine Only
12,345.00 Rupees Twelve Thousand Three Hundred Forty Five Only
123,456.00 Rupees One Lakh Twenty Three Thousand Four Hundred Fifty Six Only
123,589.95 Rupees One Lakh Twenty Three Thousand Five Hundred Eighty Nine And Paise Ninety Five Only
123,589.95 Rupees One Lakh Twenty Three Thousand Five Hundred Eighty Nine And Paise Ninety Five Only
12,345,678.00 Rupees One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight Only
123,456,789.58 Rupees Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundred Eighty Nine And Paise Fifty Eight Only
1,234,567,890.00 Rupees One Arab TwentyThree Crore FortyFive Lakh SixtySeven Thousand Eight Hundred Ninety Only
12,345,678,901.25 Rupees Twelve Arab ThirtyFour Crore FiftySix Lakh SeventyEight Thousand Nine Hundred One and Paise Twenty Five Only

I am unable to find the best solution who overcome my these type of problem.If an excel formula can solve this type of problem,please help.It should be apply on any cell in any sheet of a workbook.And,if macro required,please,macro should be enable with a formula type by me on any cell.For an example,if type an numeric figure on cell no.H45 and required formula at H46,then,apply formula should work perfectly as above result.

How is it possible through an excel formula or macro? Pl.help with best .

For more clarification,please refer attach file.

[Rudi]

Hi Pradeep,

Have a look at this webpage. It contains a macro and instructions to integrate a UDF macro (user defined function) into your workbook that will do the task to convert numbers to text using the formula: =NumWords(A1).

This formula was modified to your currency from the original macro from Microsoft found here...

The macro does have limitations based on some of the bigger numbers.

[PRADEEPB270]

Hi Rudi.

Can a macro be arrange according to limitation of attach file upto last numeric cell i.e.B15?

[Rudi]

Its not my code, so I will need to examine it closely to determine if/how it can be modified...

[PRADEEPB270]

Rudi,

Please have a look 'Revised'attach file.Perhaps,it will help to understand the Indian pattern.

[Rudi]

Hi,

See the attached for code and formula....

Number To Text.xlsm

I must credit this link for the code...which I had to clean up quite a bit...

[PRADEEPB270]

Thanks Rudi for awesome help.Please change a text,if possible, as below:-

1,234.89 Rupees One Thousand Two Hundred Thirty Four and Eighty Nine Paise

it should be as:-
Rupees One Thousand Two Hundred Thirty Four And Paise Eighty Nine Only

Means,in formula,text after 'And' start with 'Paise' and at the end, text should be 'Only'.

[HansV]

Change the SpellNumber function to

Code: Select all

Function SpellIndian(ByVal MyNumber)
    Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    ReDim place(9) As String
    place(2) = " Thousand "
    place(3) = " Lakh "
    place(4) = " Crore "
    place(5) = " Arab " ' String representation of amount
    MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if =none
    DecimalPlace = InStr(MyNumber, ".")
     ' Convert Paise and set MyNumber to Rupee amount
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))
        If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
        If Temp <> "" Then Rupees = Temp & place(Count) & Rupees
        If Count = 1 And Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            If Count > 1 And Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        End If
        Count = Count + 1
     Loop
    Select Case Rupees
    Case ""
        Rupees = "No Rupees"
    Case "One"
        Rupees = "Rupee One "
    Case Else
        Rupees = "Rupees " & Rupees
    End Select
    Select Case Paise
    Case ""
        Paise = " Only"
    Case "One"
        Paise = " And One Paisa Only"
    Case Else
        Paise = " And Paise " & Paise & " Only"
    End Select
    SpellIndian = Application.Trim(Rupees & Paise)
End Function

The rest can remain the same.

[Rudi]

Updated...

Number To Text.xlsm

[PRADEEPB270]

Many-2 thanks to you Rudi.Macro is perfect working as desired.I think it will be very useful for an Indian who works in INR currency.Once again,thanks a lot for your awesome efforts and great help.

[Rudi]

Glad I could help...I fortunately had time today as work today was fairly quite