• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Number Convert to words in Excel

Dear Sir,
I am using excel 2007, I want to convert 1000 as One Thousand Only.

I copied VBA from net and pasted as well but its not working could you please advise suitable code for the same.
 

Attachments

  • Number into words.xlsm
    14.8 KB · Views: 15
You got the code in Sheet1.

Instead, open VBE by ALT+F11, right click on VBAProject and insert Module.
Double click on Module 1 and paste the code in there.
 
Functions need to be stored in regular code modules, not the sheet module.
upload_2015-9-4_10-12-52.png

Open the VBE, go to Insert - Module, to create a regular code module. Then move your code then and you'll be able to use the SpellNumber function.

EDIT: Bah, Chihiro beat me by a second! :p
 
Dear Sir,

It worked, but I want to Put Word "Only" at the end of each amount.

eg : One Thousand Only

Please advice
 

Attachments

  • Number into words.xlsm
    21.9 KB · Views: 11
Not 100% clear if you have cases where amount may have decimal values or not. But change following portion of the code.
Code:
Select Case Dollars
        Case ""
            Dollars = "No Dollars"
        Case "One"
            Dollars = "One Dollar"
        Case Else
            Dollars = Dollars & " Dollars"
    End Select
    Select Case Cents
        Case ""
            Cents = " and No Cents"
        Case "One"
            Cents = " and One Cent"
              Case Else
            Cents = " and " & Cents & " Cents"
    End Select

Something like below

Code:
Select Case Dollars
        Case ""
            Dollars = "No Dollars"
        Case "One"
            Dollars = Dollars
        Case Else
            Dollars = Dollars
    End Select
    Select Case Cents
        Case ""
            Cents = " Only"
        Case "One"
            Cents = " and One Cent"
              Case Else
            Cents = " and " & Cents & " Cents"
    End Select

@Luke M That's what you get for being helpful and taking screenshot ;)
 
Dear Sir,

I want to put only for both cases
eg : One Thousand RUPEES and Twenty Four Paise Only

In round cases and decimal point as well.
 
Then this is what you need to do.

Code:
Select Case Dollars
        Case ""
            Dollars = "No RUPEES"
        Case "One"
            Dollars = "One RUPEE Only"
        Case Else
            Dollars = Dollars & " RUPEES"
    End Select
    Select Case Cents
        Case ""
            Cents = " Only"
        Case "One"
            Cents = " and One Paise Only"
              Case Else
            Cents = " and " & Cents & " Paise Only"
    End Select
 
Try this code..

Code:
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
  Dim Rupees, Paisas, Temp
  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 Paisas and set MyNumber to Rupee amount.
  If DecimalPlace > 0 Then
  Paisas = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
  "00", 2))
  MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
  End If
  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 Rupees
  Case ""
  Rupees = "No Rupees"
  Case "One"
  Rupees = "One Rupee"
  Case Else
  Rupees = Rupees & " Rupees"
  End Select
  Select Case Paisas
  Case ""
  Paisas = " and No Paisas Only"
  Case "One"
  Paisas = " and One Paisa Only"
  Case Else
  Paisas = " and " & Paisas & " Paisas Only"
  End Select
  SpellNumber = Rupees & Paisas
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
 
hi !
not clear on how to make this function work for all sheets whenever I open my excel. am using Excel 2010.
please could you help.
Kaushik
 
hi !
not clear on how to make this function work for all sheets whenever I open my excel. am using Excel 2010.
please could you help.
Kaushik

For complete and hassle free solution follow the below mentioned procedure..

Download spellnumber.xlam add-ins from below link

https://drive.google.com/file/d/0B7UdyO_-5jnKb1VHbmpLODBDczA/view?usp=sharing

Now follow the following procedure..

How to convert a numeric value into words (One time Activity)
1. Start Microsoft Excel
2. Go to File and Click Options
3. On the left of window click Add-Ins
4. On this window look below for Manage along with Go..
5. Click Go…
6. Add-Ins Window will pops up
7. Click browse
8. Paste there SpellNumber.xlam you've downloaded from above link
9. Click Ok
10. Now simply use spellnumber function to convert numeric values in words
For Example
If you want result in Rupees & Paisas use
=spellnumber(A1)
If you want result in Dollars & Cents use
=spellnumberUS(A1)
If you want result in Pak Rupees Style e.g. Lakh, Crore etc then use
=spellnumberPak(A1)
& If you want plain conversion without currency tag then use
=wordnum(A1)
 
thank you very much Khalid. That helps. couldn't download your file on my official laptop. hence Saved the xlsm file I had ......as xlam and pasted the link in Add in browse window. It worked. Thanks again!
 
Back
Top