Solved

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

Posted on 2014-02-12
11
1,788 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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 48

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
 
LVL 8

Expert Comment

by:itjockey
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now