Solved

In MS Excel convert numbers or amount into words by using formula

Posted on 2014-02-12
11
1,940 Views
Last Modified: 2014-04-08
Hi there,

Attached please find the MS Excel file. In which there are two sheets. Nos_Words and Active_Sheet.

Sheet with the name Nos_Words has some sort of table to convert the numeric values into words. It is working fine. Like whatever the value is in C5 of the Active_Sheet, it converts it into the required wording. And then in Active_Sheet it displays the words from the F4 of Nos_Words sheet.

What i need to do is this, display the amount or numbers in Words in my whole list as shown in Active_Sheet. Please help!

I have tried the VBA sample code from the Microsoft site “Function SpellNumber” http://support.microsoft.com/kb/213360  but the attached sheet is close to my requirements.

Basically i am looking for a formula not VBA function to convert numbers into words as it is doing in the Nos_Words sheet of the attached file. However now i need to apply this on the whole list on the Active_Sheet.

Best Regards
Numbers-to-words.xlsx
0
Comment
Question by:masmatc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39852993
Hi,

pls try
'-----------------------------------------------------------*------
Function SpellNumberIndian(ByVal MyNumber)
'-----------------------------------------------------------*------
Dim Crore, Lakh, Rupees, Paise, Temp
Dim DecimalPlace As Long, Count As Long
Dim myLakhs, myCrores
Dim Result As String

ReDim Place(9) As String
Place(2) = " Thousand "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")

' Convert Paise and set MyNumber to Rupees amount.
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
On Error Resume Next
myCrores = MyNumber \ 10000000
myLakhs = (MyNumber - myCrores * 10000000) \ 100000
MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000
On Error GoTo 0

Count = 1
Do While myCrores <> ""
Temp = GetHundreds(Right(myCrores, 3))
If Temp <> "" Then Crore = Temp & Place(Count) & Crore
If Len(myCrores) > 3 Then
myCrores = Left(myCrores, Len(myCrores) - 3)
Else
myCrores = ""
End If
Count = Count + 1
Loop

Count = 1
Do While myLakhs <> ""
Temp = GetHundreds(Right(myLakhs, 3))
If Temp <> "" Then Lakh = Temp & Place(Count) & Lakh
If Len(myLakhs) > 3 Then
myLakhs = Left(myLakhs, Len(myLakhs) - 3)
Else
myLakhs = ""
End If
Count = Count + 1
Loop

Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Crore
Case "": Crore = ""
Case "One": Crore = " One Crore "
Case Else: Crore = Crore & " Crore "
End Select

Select Case Lakh
Case "": Lakh = ""
Case "One": Lakh = " One Lakh "
Case Else: Lakh = Lakh & " Lakh "
End Select

Select Case Rupees
Case "": Rupees = " Zero"
Case "One": Rupees = " One"
Case Else: Rupees = Rupees
End Select

Select Case Paise
Case "": Paise = " Only "
Case "One": Paise = " and Paise One Only "
Case Else: Paise = " and Paise " & Paise & " Only "
End Select

If Rupees = "Zero " And (Crore = "" Or Lakh = "" Or Paise = "") Then
    Result = "Rupees " & Crore & Lakh & Paise
Else
    Result = "Rupees " & Crore & Lakh & Rupees & Paise
End If

SpellNumberIndian = Replace(Result, "  ", " ")

End Function

Open in new window

EDIT Better space management

Regards
0
 
LVL 24

Expert Comment

by:Steve
ID: 39853069
The attached file has something close which can be converted to a single cell formula.

Does this look about right?
Numbers-to-words.xlsx
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 39853116
The attached workbook does the task with a single formula:

="Rupees "&VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"000000000"),9),2),Nos_Words!$B$2:$C$101,2,FALSE)&IF(VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"000000000"),9),2),Nos_Words!$B$2:$C$101,2,FALSE)="",""," Crore ")&VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),7),2),Nos_Words!$B$2:$C$101,2,FALSE)&IF(VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),7),2),Nos_Words!$B$2:$C$101,2,FALSE)="",""," Lakh ")&VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),5),2),Nos_Words!$B$2:$C$101,2,FALSE)&IF(VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),5),2),Nos_Words!$B$2:$C$101,2,FALSE)="",""," Thousand ")&VLOOKUP("0"&LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),3),1),Nos_Words!$B$2:$C$101,2,FALSE)&IF(VLOOKUP("0"&LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),3),1),Nos_Words!$B$2:$C$101,2,FALSE)="",""," Hundred ")&IF(OR(VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"000000000"),9),2),Nos_Words!$B$2:$C$101,2,FALSE)&VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),7),2),Nos_Words!$B$2:$C$101,2,FALSE)&VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),5),2),Nos_Words!$B$2:$C$101,2,FALSE)&VLOOKUP("0"&LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),3),1),Nos_Words!$B$2:$C$101,2,FALSE)="",VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),2),2),Nos_Words!$B$2:$C$101,2,FALSE) = ""), "","and ")&VLOOKUP(LEFT(RIGHT(TEXT(INT($C5),"00000000000000"),2),2),Nos_Words!$B$2:$C$101,2,FALSE)&IF(VLOOKUP(TEXT((C5-INT(C5))*100,"00"),Nos_Words!$B$2:$C$101,2,FALSE)="",""," Paise ")&VLOOKUP(TEXT((C5-INT(C5))*100,"00"),Nos_Words!$B$2:$C$101,2,FALSE)&" Only"

This is after converting the vlookup numbers on the second page to text in format "00".

See attached.
Numbers-to-words.xlsx
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39853995
Mr.Steve,
Interesting formula tomorrow I will try this......

Just surfing in EE

Thanks
0
 

Author Comment

by:masmatc
ID: 39862288
Sorry about the late response at my end.

Steve apparently the formula is promising. However if the amount exceed than 100,00,000,00 then the result seems old. May be you thinking this is my limit for numbers, but it is not.

Great job! building the formula and it works fine with the maximum limit of 100,00,000,00. Can it be altered to work for any given amount.

Like can you use trillions, billions, millions with rupees and cover up any given amount to spell it. Thanks for your efforts!

And for  Rgonzo1971:

Same with the SpellNumberIndian function like if the amount exceed 100 Crore (One Hundred Crore) then function give old result. Again may be you thinking my limit is 100 crore, no it's not.

So can you please  use trillions, billions, millions with rupees and cover up any given amount to spell it. Again thanks for your effort also.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39862530
Hi,

Excel has its own limits (if you want paise precision: this is the biggest number you can have is 9999999999999,99 should it be spelled :

Rupees Nine Hundred Ninety Nine Thousand Nine Hundred Ninety Nine Crore Ninety Nine Lakh Ninety Nine Thousand Nine Hundred Ninety Nine and Paise Ninety Nine Only )

if yes

pls try
'-----------------------------------------------------------*------
Function SpellNumberIndian(ByVal myNumber)
'-----------------------------------------------------------*------
Dim Crore As String, Lakh As String, Rupees As String, Paise As String, Temp As String
Dim DecimalPlace As Long, Count As Long
Dim myLakhs As String, myCrores As String
Dim Result As String
Dim myDecNumber As Variant


ReDim Place(9) As String
Place(2) = " Thousand "

' String representation of amount.
myNumber = Trim(Str(myNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(myNumber, ".")

' Convert Paise and set myDecNumber to Rupees amount.
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(myNumber, DecimalPlace + 1) & "00", 2))
myNumber = Trim(Left(myNumber, DecimalPlace - 1))
End If
On Error Resume Next
myDecNumber = CDec(myNumber)
'myTrillions = myDecNumber \ 1000000000000# 't
'myBillions = myDecNumber \ 1000000000 'b
myCrores = Fix(myDecNumber / 10000000)
myLakhs = Fix((myDecNumber - myCrores * 10000000) / 100000)
myNumber = CStr(myDecNumber - myCrores * 10000000 - myLakhs * 100000)
On Error GoTo 0

Count = 1
Do While myCrores <> ""
Temp = GetHundreds(Right(myCrores, 3))
If Temp <> "" Then Crore = Temp & Place(Count) & Crore
If Len(myCrores) > 3 Then
myCrores = Left(myCrores, Len(myCrores) - 3)
Else
myCrores = ""
End If
Count = Count + 1
Loop

Count = 1
Do While myLakhs <> ""
Temp = GetHundreds(Right(myLakhs, 3))
If Temp <> "" Then Lakh = Temp & Place(Count) & Lakh
If Len(myLakhs) > 3 Then
myLakhs = Left(myLakhs, Len(myLakhs) - 3)
Else
myLakhs = ""
End If
Count = Count + 1
Loop

Count = 1
Do While myNumber <> ""
Temp = GetHundreds(Right(myNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(myNumber) > 3 Then
myNumber = Left(myNumber, Len(myNumber) - 3)
Else
myNumber = ""
End If
Count = Count + 1
Loop

Select Case Crore
Case "": Crore = ""
Case "One": Crore = " One Crore "
Case Else: Crore = Crore & " Crore "
End Select

Select Case Lakh
Case "": Lakh = ""
Case "One": Lakh = " One Lakh "
Case Else: Lakh = Lakh & " Lakh "
End Select

Select Case Rupees
Case "": Rupees = " Zero"
Case "One": Rupees = " One"
Case Else: Rupees = Rupees
End Select

Select Case Paise
Case "": Paise = " Only "
Case "One": Paise = " and Paise One Only "
Case Else: Paise = " and Paise " & Paise & " Only "
End Select

If Trim(Rupees) = "Zero" And Crore = "" And Lakh = "" Then
    Result = Rupees & Paise
Else
    Result = "Rupees " & Crore & Lakh & IIf(Trim(Rupees) = "Zero", "", Rupees) & Paise
End If

SpellNumberIndian = Replace(Result, "  ", " ")

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

Open in new window

Regards
0
 
LVL 24

Expert Comment

by:Steve
ID: 39864596
Hi there, for the formula method I have used specifically not using VBA is as requested...

The maximum my formula is set to work to is...
"Rupees Ninety nine Crore Ninety nine Lakh Ninety nine Thousand Nine Hundred and Ninety nine Paise Ninety nine Only"
This is the same limit as your original formula, your original question states:
"Basically i am looking for a formula not VBA function to convert numbers into words as it is doing in the Nos_Words sheet of the attached file."
The formula I have written does what the file is doing in the "Nos_Words" sheet.
To enlarge the formula to take in longer values will require a more complicated formula, which you would need to explain how it works to us.

As the formula is already quite a beast, I would be inclined towards the VBA method as the numbers get bigger (but you specifically asked not to use VBA).

How big do your numbers go?
How would the largest number read?

ATB
Steve.
0
 

Author Comment

by:masmatc
ID: 39879141
thanks for your efforts and comments

Steve, i think you have the ability to ask the good logical questions, and that is also helping me to solve the issue in hand. Now how big do my numbers go...for now i think i can live with the following number as a limit:

99,99,99,99,99,99,99,99,9,99.99

And how would the largest number read?...it should read as follows:

Rupees Ninety nine Shankh Ninety nine Padm Ninety nine Neel Ninety nine Khrab Ninety nine Arb Ninety nine crore Ninety nine lakh Ninety nine thousand nine hundred  and Ninety nine Paise Ninety nine only

Please see the attached file for the naming convention explanation for Shankh, Padm, Neel, Khrab, Arb.

For Rgonzo1971, Can you please edit your VBA code to spell out the amounts according to the above mentioned biggest number and its spell (read) requirement.

Experts your help in this regard will be life saving!

Note: i wish i know this requirement at the time of posting my original question, Please bear with me.
Naming-Convention.xlsx
0
 
LVL 24

Expert Comment

by:Steve
ID: 39882293
Hello there Masmatc, have you tried typing the largest number into an excel cell and seeing the results... as Excel has a "bit of trouble" with such large numbers.

So it would seem that you may encounter some problems in excel when trying to go to such a high number.

Can you test the limits of excel for your numbers and see if your results match mine.

Thanks,
Steve.
0
 

Author Closing Comment

by:masmatc
ID: 39985685
Thanks for your efforts.
0
 

Author Comment

by:masmatc
ID: 39985690
Did not get chance to figure out what trouble MS Excel has with large number as you mentioned Steve. But if need arise i will do this and basically start over again, may be with a new question.

Also like to thank Rgonzo1971 for posting the function as well. Do not know how to split the points.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question