Wednesday, January 18, 2012

How To Convert Number Into Words In MS Excel ? (Example 100 = Hundred Dollars)

MS Excel has so many Text, Financial and Statistical Formulas. But Unfortunately, we don't have the direct formula to convert Number to the Text Words. Fortunately, we have a Macro Code to act for the same.


I found a Macro Code to convert Number to the English Text Words, and I did few modifications to customize.



Steps Need to Follow :-


1. Open MS Excel Workbook
2. Click Alt + F11
    Then, Microsoft Visual Basic Window opens
3. Click Alt + I, M (or Click Module from Insert Menu Bar)
    Then, Module Window opens
4. Copy the below Code (Short Cut: Ctrl + C (or) Alt + E, C (or) Right Click, C (or) Ctrl + Insert)



Option Explicit
'Main Function
Function NumWords(ByVal MyNumber)
Dim Rupees, Paises, 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 Paises and set MyNumber to Rupee amount.
If DecimalPlace > 0 Then
Paises = 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 Paises
Case ""
Paises = " and No Paises"
Case "One"
Paises = " and One Paise"
Case Else
Paises = " and " & Paises & " Paises"
End Select
NumWords = Rupees & Paises
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



5. Paste into the Module Window (Short Cut: Ctrl + V (or) Alt + E, V (or) Right Click, V)
 
6. Close Microsoft Visual Basic Window


7. That's All !!!


How can I Test ?


Through the above Macro Code we created a new formula "NumWords".


Now, we can use "NumWords" Formula to Convert Number to the Text Words.


Example:


1. Enter Number into the Formula


=numwords(100)


The above formula results "One Hundred  Rupees and No Paise"


2. Using Cell Reference into the Formula


Let Say, A1 cell contains 1500 and we want to text words in A2


Then the Formula in A2 cell is =numwords(A1)


A2 cell results "One Thousand Five Hundred  Rupees and No Paise"


Now, You can test with more examples.

184 comments:

  1. wah, gud 1

    -P.V.Narasimha Rao

    ReplyDelete
    Replies
    1. Thanks..!
      User can also try free GWORD Multi Currency Converter to Words.
      Link - www.gwordformula.blogspot.com

      Delete
    2. Hi, Thanks.
      User can also try free addin formula which auto convert numbers or amount to words or rupees.
      Download Link - www.xltool.in | www.gwordformula.blogspot.com

      Delete
  2. how to change this formula to get rupees before numbers i.e. Rupees one thousand & paise zero)

    ReplyDelete
    Replies
    1. Hi, Thanks.
      User can also try free GWORD addin formula which auto convert numbers or amount to words or rupees.
      Download Link - www.xltool.in | www.gwordformula.blogspot.com

      Delete
  3. @ Tiwari

    first use the above steps to get the formula as it is..

    then use Ctrl+H to replace Rupees as Nil.. Ctrl+H, enter Rupees in Find what field.. then use short cut Alt+A (To replace all).. now the result will be One Thousand & Paise Zero

    Now, use the formula ="Rupees"&" "&A1 .. (here A1 refers to the first result ie., One thousand & Paise Zero..)

    Thats all.. you will get the result now as "Rupees One thousand Paise zero

    Regards,
    Saran

    ReplyDelete
    Replies
    1. Dear Mr.Saran
      Will you please helps us how to convert only numbers (No currency name no decimal point).
      For example to convert 1001, we want to convert One thousand onne.

      Delete
    2. how to it can be obtained in nepali or hindi letter

      Delete
    3. Hey i can help u i have created code for that

      Delete
    4. Can you send the code for Marathi or Hindi?

      Delete
    5. Mr.Keshav Acharya,

      Can you send me the code for Marathi or Hindi?

      Delete
    6. Dear Mr. Salam,

      Can you help also convert to dirham insted of rupees?

      Delete
  4. It really hepls...Thank you

    ReplyDelete
  5. Thanks for giving us so useful tricks. Is it possible to remove paise value from output result????

    Thanks
    Arvind

    ReplyDelete
    Replies
    1. @ Arvind,

      Just go to code using alt+F11.

      Replace paise with nil (ctrl+H, enter paise as find what and dont enter any thing in replace with and click ok)

      Delete
    2. @saran .. after replacing as you have mentioned here .. its showing "and no" at the end .. how to remove that ..

      Delete
    3. Round the number to 0 digits as below
      MyNumber = Round(Trim(Str(MyNumber)), 0)

      Delete
    4. Replace paise with Only and with MT(blank) & No with MT(blank) (ctrl+H, enter paise as find what and dont enter any thing in replace with and click ok)

      Delete
  6. error message appeared as "outside procedure error"

    ReplyDelete
    Replies
    1. true, 'cause the code breaks when u replace the word!

      could smbody explain, how to change it please?

      Delete
    2. Be sure you have change all the WORDS you need to change or surely it will be error. I wonder it did not work for you,but i deed.

      Delete
    3. i change the currency into pesos, and mine did just work. be sure you edit things corectly

      Delete
  7. Sir But when we goes for Rs-12345678.00 then it shows like US language (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We write it Like (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa) please send it at my Email Id (pankajsingh4246@gmail.com) or wright down here. It will be highly thankful

    ReplyDelete
    Replies
    1. YES , thats wht i also want...

      Delete
    2. YES , thats wht i also want... please send it at my Email Id (ramen.dce09@gmail.com)

      Delete
    3. Yes it's true.. I want the same. When I search on net. I found the same coding in US language. Perhaps this coding is extract from there and change Dollar into Rupees and cents into paise. But it's need more change.

      Delete
  8. This is very simple way to learn it

    Thanks

    D Rajendra

    ReplyDelete
  9. Simple and Super coding...
    Its very useful...
    Thank u.

    ReplyDelete
    Replies
    1. sir,
      after saving it's working. if yes please guide me sir

      Delete
  10. Thank u for ur coding.. Its really super

    ReplyDelete
  11. when used
    the above formula it gives this result

    Two Thousand Sixty Three Rupees and Eighty Seven Paises
    Rs. 2,063.875/-

    instead of this

    Two Thousand Sixty Three Rupees and Eighty Eight Paises
    Rs. 2,063.88/-

    means it doesn't round off to the 2 decimal points

    ReplyDelete
    Replies
    1. you have to use "round" function while getting the answer itself.

      Delete
  12. how to change the formula to get the two decimal point result

    ReplyDelete
  13. I want to convert Date in text format i.e. 17/08/1980 as Seventeen August Nineteen Eighty. Please help me for that

    ReplyDelete
  14. Sir But when we goes for Rs-12345678.00 then it shows like US language (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We write it Like (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa) please send it at my Email Id (pankajsingh4246@gmail.com) or wright down here. It will be highly thankful

    ReplyDelete
  15. Sir But when we goes for Rs-12345678.00 then it shows like US language (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We write it Like (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa) please send it at my Email Id (pankajsingh4246@gmail.com) or wright down here. It will be highly thankful

    ReplyDelete
  16. Thank you Sir, it is indeed a great help, i just edited the Rupee to Peso and it works...You are amazing.

    ReplyDelete
    Replies
    1. Hi can u give me the copy of code that is already chage from rupees to peso?

      Delete
  17. i need this formula to Qatar riyal for example
    125,524.50
    Qatar Riyal One hundred twenty five thousand five hunded twenty four & Dirham fifty only

    Thanks
    Farr_ex@yahoo.com`

    ReplyDelete
  18. it is only working on one sheet which we have run the Macro..if i want to use the same formula whenever i need.. plz suggest

    ReplyDelete
  19. Greetings of the Day...........

    Many Thanks

    ReplyDelete
  20. Thanks for this
    I m in problem to how to fine

    ReplyDelete
  21. Thanks for this
    I m in problem to how to fine

    ReplyDelete
  22. Hi I need to convert numbers on a scale, to a set of words. For instance, if the score falls between 50 and 60, then the paragraph "this candidate has strengths in this area ..... and weaknesses in this area........" will be displayed.

    Can anyone help me with this?

    ReplyDelete
  23. How to add "Only" at the end?

    ReplyDelete
  24. Hi, But when it goes for Rs-12345678.00 then it shows like US denomination (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We mention it as (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa). How to derive on the same. Kindly help

    ReplyDelete
    Replies
    1. Hi, But when it goes for Rs-12345678.00 then it shows like US denomination (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We mention it as (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa). How to derive on the same. Kindly help

      Delete
  25. Thanks man it really helped

    ReplyDelete
  26. thanks brother..loving send some more trics

    ReplyDelete
  27. wen i close the excel it does not automaticly save this formula. do i hav to enter the same code wenever i hav to do that conversion of number to text?

    ReplyDelete
  28. wen i close the excel it does not automaticly save this formula. do i hav to enter the same code wenever i hav to do that conversion of number to text?

    ReplyDelete
  29. I tried this its not working all of a sudden

    ReplyDelete
  30. How to Convert $4000.20 to USD Four Thousands and Twent Cents

    ReplyDelete
  31. Dear Sir,
    Thank u it is very use full, But when we goes for Rs-12345678.00 then it shows like US language (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We write it Like (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa) please send it at my Email Id (1988sarfaraz@gmail.com) or wright down here. It will be highly thankful

    ReplyDelete
  32. Thank u it is very use full, But when we goes for Rs-12345678.00 then it shows like US language (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We write it Like (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa) please send it at my Email Id (shanafees@gmail.com) or wright down here. It will be highly thankful

    ReplyDelete
  33. Thank you so much for this Saran K Royal This formula helped me ease me work load very much

    ReplyDelete
  34. sir it is very useful but to get the result as "Rupees One thousand Paise Zero" I Replace the "Rupees" by using Ctrl+H but the result is not coming.

    I want the syntax to be written in excel file after replacing Rupees. You asked to use the formula ="Rupees"&" "&A1. but I put the same in excel file after =Numwords. I can't understand how to run the furmula. please give the full syntax.
    where to put this? Please help.

    ReplyDelete
  35. hy,my question is i can not show amount in paisa only show in rupess

    ReplyDelete
  36. Really very help full, but is it possible to convert this formula is MS Access. a filed name "TotalAmount" is in numbers, and another filed is created "InWords" how to make it in Access, I had copied the same formula in Access VB, and called in on exit of TotalAmount did not work, I called as '=NumWords[(TotalAmount)]' gives me error. Then I called on click "InWords" 'NumWords[(TotalAmount)] still did not work. Here I required your assistance please "javedybutt@gmail.com". But In Excel it is working 100%

    ReplyDelete
  37. woooooooooow very good helpful thanks

    ReplyDelete
  38. Hello Sir,

    Could you please help me in converting below figures into Indian format.

    When I am applying this formula, this show me as-

    (i)11,10,157.50-- One Million One Hundred Ten Thousand One Hundred Fifty Seven Rupees and Fifty Paises
    or
    (ii)2,54,78,936-- Twenty Five Million Four Hundred Seventy Eight Thousand Nine Hundred Thirty Six Rupees and No Paises


    However, I want these figures in the form of:
    (i)11,10,157.50-- Rupees Eleven Lakh Ten Thousand One Hundred Fifty Seven and Fifty Paisa.

    (ii)2,54,78,936-- Rupees Two Core Fifty Four Lakh Seventy Eight Thousand Nine Hundred Thirty Six.

    Can you please help me with this, giving me the correct formula, so that I can use it as per my accordance.

    Thanks & Regards
    Sumit Salwan

    ReplyDelete
    Replies
    1. please send me what is your reply

      Delete
    2. Dear Friend i think you go to rupees function its 100% solve your query!

      Delete
    3. if you got the solution than please send me to my ID himmatgoswami@gmail.com

      Delete
  39. My dear I'm very thank full to you...

    Thanks you very much.Im searching for this for a long time..Thanx alot.

    Suresh.S

    ReplyDelete
  40. if i type the number just like 123456789.45 afterwords i want the result such as Twelve Crore Thirty Four Lac Fifty Six Thousands Seven Hundred Eighty Nine Rupees Forty Five Paise. How can i get that result?
    send me the solution to my id apshirke@email.com

    ReplyDelete
  41. if i type the number 123456789.45 and i want the result as Twelve Crore Thirty Four Lac Fifty Six Thousand Seven Hundred Eighty Nine Rupees Forty Five Paise. How can i get that? send me the result on my email id.

    ReplyDelete
  42. if i type the number 123456789.45 and i want the result as Twelve Crore Thirty Four Lac Fifty Six Thousand Seven Hundred Eighty Nine Rupees Forty Five Paise. How can i get that? send me the result on my email id.

    ReplyDelete
  43. if i type the number just like 123456789.45 afterwords i want the result such as Twelve Crore Thirty Four Lac Fifty Six Thousands Seven Hundred Eighty Nine Rupees Forty Five Paise. How can i get that result?
    send me the solution to my id apshirke@email.com

    ReplyDelete
    Replies
    1. if you got the solution please send the same to me at himmatgoswami@gmail.com

      Delete
  44. Very wonderful coding.. Thanks for sharing..

    ReplyDelete
  45. Thank u it is very use full, But when we goes for Rs-12,345,678.00 then it shows like US language (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We write it Like (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa) please send it at my Email Id (piyushksoni7@gmail.com) or wright down here. It will be highly thankful

    ReplyDelete

  46. i need this formula to AED for example
    125,524.50
    AED One hundred twenty five thousand five hunded twenty four & Fils fifty only

    Thanks
    sairam2610@gmail.com

    Reply pls to email. thanks a ton

    ReplyDelete
  47. I want the formula to convert Aed amount into words in Ecel

    ReplyDelete
  48. This comment has been removed by the author.

    ReplyDelete
  49. This comment has been removed by the author.

    ReplyDelete
  50. This comment has been removed by the author.

    ReplyDelete
  51. if i type the number just like 123456789.45 afterwords i want the result such as Twelve Crore Thirty Four Lac Fifty Six Thousands Seven Hundred Eighty Nine Rupees Forty Five Paise. How can i get that result?
    send me the solution to my id mahadevdamakale1976@gmail.com

    ReplyDelete
  52. Excellent. But is there any way so if there is no paisa available - no paisa will appear ?

    ReplyDelete
  53. Wanted to share an online tool that converts numbers to text:
    http://knowpapa.com/num2txt/

    This is for people who are afraid of coding or are not that tech savvy like me.

    ReplyDelete
  54. It helped a lot..

    ReplyDelete
  55. Dear All
    Actually This person is not a programmer
    its just copying from url= http://support.microsoft.com/kb/213360


    Hllo
    Dear if you are a real programmer then Convert this value from your formula "24556825.23"

    n according to your programming your result is 9,99,999 Nine Hundred ninety nine thousand nine hundred ninety nine only(you can check your flash dear, on the top of your website)

    while its answer should be Nine Lac Ninety Nine Thousand.....................

    ReplyDelete
    Replies
    1. its has been work brother i tried as per your quotes: answers below

      Twenty Four Million Five Hundred Fifty Six Thousand Eight Hundred Twenty Five Rupees and Twenty Three Paises

      Delete
  56. This comment has been removed by the author.

    ReplyDelete
  57. Dear All,

    I have a queries, i was working in dubai, here the currency status is different it's called AED, can anyone update me the formula for this? ( Number to Words )

    also this application not save as a standard in ms excel, all the time we have to save it by manual only?

    Please revert me on this email ( sa.sayedabu@gmail.com)

    ReplyDelete
  58. thanks, Working fine., but when i closed this settings also gone, i.e this is temporary solution. i need to permanent solution, anyone send the solution..
    Mail to abubacker@themodel,ae

    ReplyDelete
  59. Sir on every sheet i have to put this formulla how i can put it permanent on excel 2013

    ReplyDelete
  60. Hello Sir,

    Could you please help me in converting below figures into Indian format.

    When I am applying this formula, this show me as-

    (i)11,10,157.50-- One Million One Hundred Ten Thousand One Hundred Fifty Seven Rupees and Fifty Paises
    or
    (ii)2,54,78,936-- Twenty Five Million Four Hundred Seventy Eight Thousand Nine Hundred Thirty Six Rupees and No Paises


    However, I want these figures in the form of:
    (i)11,10,157.50-- Rupees Eleven Lakh Ten Thousand One Hundred Fifty Seven and Fifty Paisa.

    (ii)2,54,78,936-- Rupees Two Core Fifty Four Lakh Seventy Eight Thousand Nine Hundred Thirty Six.

    Can you please send me the formula to this EMAIL ID- deepak171106@yahoo.com to help me with this, giving me the correct formula, so that I can use it as per my accordance.

    Thanks & Regards
    Deepak Rawat

    ReplyDelete
  61. it is working...but after i saved and again opened the document..it is not working...pls reply

    ReplyDelete
  62. it is working...but after i saved and again opened the document..it is not working...pls reply

    ReplyDelete
  63. Dear Sir,

    it is working ...but after i can't save this excell file solution please..??

    ReplyDelete
  64. Hello, after completing my work, I couldn't save this file.
    What should do? to save the file and regular use.
    Thanks & regards
    Abdul Aziz

    ReplyDelete
  65. facing problem while save the excel sheet.. how to solve it.
    error is:
    the following features cannot be saved in macro-free workbooks:


    -VB PROJECT

    plz give me reply as soon as possible

    ReplyDelete
  66. getting error #NAME?

    ReplyDelete
  67. can I have this code too on nayyar_jyoti@rediffmail.com as want to conver 1,22,30,456 as Rs. One Crore Twenty Two lakhs Thitrty Thousand Four Hub=ndred Fifty Six Only

    ReplyDelete
  68. Very Nice but there is a problem that it display NO PAISA its wrong. www.100acres.in

    ReplyDelete
    Replies
    1. you can erase last words, but there is also a problem. that we can not replace because its display with formula.

      Delete
  69. sir
    only I need inform like 32.5 means Thirty two point five only

    ReplyDelete
  70. Sir But when we goes for Rs-12345678.00 then it shows like US language (Twelve Million Three Hundred Forty Five Thousand Six Hundred Seventy Eight Rupees and No Paises)but in India We write it Like (One Crore Twenty Three Lakh Forty Five Thousand Six Hundred Seventy Eight and Zero Paisa) please send it at my Email Id (bjgopani1393@gmail.com) or wright down here. It will be highly thankful

    ReplyDelete
  71. This comment has been removed by the author.

    ReplyDelete
  72. Hello Sir,

    Could you please help me in converting below figures into Indian format.

    When I am applying this formula, this show me as-

    (i)11,10,157.50-- One Million One Hundred Ten Thousand One Hundred Fifty Seven Rupees and Fifty Paises
    or
    (ii)2,54,78,936-- Twenty Five Million Four Hundred Seventy Eight Thousand Nine Hundred Thirty Six Rupees and No Paises


    However, I want these figures in the form of:
    (i)11,10,157.50-- Rupees Eleven Lakh Ten Thousand One Hundred Fifty Seven and Fifty Paisa.

    (ii)2,54,78,936-- Rupees Two Core Fifty Four Lakh Seventy Eight Thousand Nine Hundred Thirty Six.

    Can you please send me the formula to this EMAIL ID- bjgopani1393@gmail.com to help me with this, giving me the correct formula, so that I can use it as per my accordance.

    Thanks & Regards
    Gopani Bhavesh

    ReplyDelete
  73. thank u..
    but i need not the paisa detail
    i.e., one hundred and fifty five only

    ReplyDelete
    Replies
    1. if u know this , send me to mail through mmdmusthak@gmail.com

      Delete
  74. Thanks!
    Its is saved my lots of time.
    But i want the currency in AED or Dirhams. Could you please show me, how to change the currency from Rupeees to Dirham since i am working in Dubai.
    Email:raafimohammad@gmail.com

    ReplyDelete
  75. thanking you ,
    But once close the application, next time the format will not display the cell... what is do?

    pls send reply to mail id ---- diveesbiotechs@gmail.com

    ReplyDelete
  76. there is no any Module button in Insert Menu

    ReplyDelete
  77. Great Man!!! Thanks It was really of great help!!

    ReplyDelete
  78. Thank you so much... it really helped me to discontnue the data entry work of typing Amount in words. Thanks again - payal

    ReplyDelete
  79. Lovely Buddy :) It's really like a dream comes true :)
    Stayblessed

    ReplyDelete
  80. thank you.
    is it work with lotus symphony?.
    if not wat can i do for it?

    ReplyDelete
  81. its very nice but i need for Saudi Arabia like (insted of Rupee -Riyal-Rupees-Riyals-Paisa-Halala-Paises-Halalas)
    Rupee
    Paisa
    Rupees
    Paises
    Riyal
    Riyals
    Halala
    Halalas

    ReplyDelete
  82. Thanks.
    Also we can Try this Excel Add-in - CLICK
    http://www.xl.nikash.in/2012/06/convert-number-to-english-indian-rupees_8693.html

    ReplyDelete
  83. If you create a assign a keyboard shortcut to the code below and save it to your personal workbook

    Selection.Value = (numwords(Selection.Value))

    you can change the selection to words.

    ReplyDelete
  84. This comment has been removed by the author.

    ReplyDelete
  85. it is not permanent....its not work after closed.

    ReplyDelete
  86. thanks., it works ^^.,

    ReplyDelete
  87. Dear All,
    I have changed the code in to "Dirhams" & "Fils" format to use in UAE. (102.50 = One hundred two Dirhams and Fifty Fils)
    Please copy the below code and paste in the module window.

    Option Explicit
    'Main Function
    Function NumWords(ByVal MyNumber)
    Dim Dirhams, Fils, 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 Fils and set MyNumber to Dirhams amount.
    If DecimalPlace > 0 Then
    Fils = 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 Dirhams = Temp & Place(Count) & Dirhams
    If Len(MyNumber) > 3 Then
    MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
    MyNumber = ""
    End If
    Count = Count + 1
    Loop
    Select Case Dirhams
    Case ""
    Dirhams = "No Dirhams"
    Case "One"
    Dirhams = "One Dirhams"
    Case Else
    Dirhams = Dirhams & " Dirhams"
    End Select
    Select Case Fils
    Case ""
    Fils = " and No Fils"
    Case "One"
    Fils = " and One Fils"
    Case Else
    Fils = " and " & Fils & " Fils"
    End Select
    NumWords = Dirhams & Fils
    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

    ReplyDelete
    Replies
    1. Can i get help to write:

      2098 TWENTY NINETYEIGHT


      6704 SIXTYSEVEN O FOUR

      0435 ZERO FOUR THIRTYFIVE

      Will be greatful if i can receive mail on adhimaloy@gmail.com

      Delete
  88. I want to convert Metric weight from numbers to words.

    eg.
    1. 10.50 Metric Tons (MT) should write as "10 Tons and 500 kgs"
    2. 12.345 MT = Twelve Tons and Three hundred Forty Five Kgs

    Need 3 decimal places atleast and 4 is even better.
    max 9999.9999 is how i want.

    Can someone please help?

    ReplyDelete
  89. dear sir,
    its done, when i reopen this excell file their is not working how to always run this Formula please send me ( josejohn39@gmail.com )

    ReplyDelete
  90. dear sir,
    its done, when i reopen this excell file their is not working how to always run this Formula please send me ( josejohn39@gmail.com )

    ReplyDelete
  91. I do not want to use visual basic code in excel. Is there any other way?

    ReplyDelete
  92. I am getting compilation error on VB page every time when I execute this formula.

    ReplyDelete
  93. HI i want the following result
    5,03,200 (Five Lakh Three Thousand Two Hundred Only) It is possible?

    ReplyDelete
  94. Sir when i select 357218=Three Lakh Fifty Seven Thousand Two Hundred Eighteen,
    Bt It shows Three Hundred Fifty seven Thousand Two Hundred Eighteen
    Wht can i do now

    ReplyDelete
  95. Thank you its works perfectly....
    My Name is Munna +91 8253819060
    Thank you verry much

    ReplyDelete
  96. Hello Sir,

    Could you please help me in converting below figures into Indian format.

    When I am applying this formula, this show me as-

    (i)11,10,157.50-- One Million One Hundred Ten Thousand One Hundred Fifty Seven Rupees and Fifty Paises
    or
    (ii)2,54,78,936-- Twenty Five Million Four Hundred Seventy Eight Thousand Nine Hundred Thirty Six Rupees and No Paises


    However, I want these figures in the form of:
    (i)11,10,157.50-- Rupees Eleven Lakh Ten Thousand One Hundred Fifty Seven and Fifty Paisa.

    (ii)2,54,78,936-- Rupees Two Core Fifty Four Lakh Seventy Eight Thousand Nine Hundred Thirty Six.

    Can you please help me with this, giving me the correct formula, so that I can use it as per my accordance.

    Thanks & Regards
    santosh jadhav santoshjadhavmum@gmail.com

    ReplyDelete
  97. Go to same module find pressing ctrl+f find rupees and replace all with $, euro, Dinar, Darham or any other you need, also do same for decimal part of numbers find paisas and replace all with cent, halalas or any other symbol you need.
    Regards
    Muhammad Naeem

    ReplyDelete
  98. Can i get help to write:

    2098 TWENTY NINETYEIGHT


    6704 SIXTYSEVEN O FOUR

    0435 ZERO FOUR THIRTYFIVE

    Will be greatful if i can receive mail on adhimaloy@gmail.com

    ReplyDelete
  99. This formula works only for thousand but not for lacs. how to convert in word of lacs amount
    pls sent the solution in my ID - shyamkydganj@gmail.com

    ReplyDelete
    Replies
    1. This formula works only for thousand but not for lacs. how to convert in word of lacs amount
      pls sent the solution in my ID - shyamkydganj@gmail.com

      Delete
  100. This formula works only for thousand but not for lacs. how to convert in word of lacs amount
    pls sent your answer in my id hina27288@gmail.com

    ReplyDelete
  101. Dear Sirs, I Try to use this conversion. it is worked. but after save this file. not working. please guide me sir

    ReplyDelete
  102. Sir,
    when I close the file then reopen it is not working. kindly guide

    ReplyDelete
  103. Dear All,
    I have changed instead of No Paise. it's come Only. But after save this file. not working please guide me anyone please.
    Option Explicit
    'Main Function
    Function NumWords(ByVal MyNumber)
    Dim Rupees, Paises, 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 Paises and set MyNumber to Rupee amount.
    If DecimalPlace > 0 Then
    Paises = 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 Paises
    Case ""
    Paises = " Only"
    Case "One"
    Paises = " and One Paise Only"
    Case Else
    Paises = " and " & Paises & " Paises Only"
    End Select
    NumWords = Rupees & Paises
    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

    ReplyDelete
  104. Dear Friends,
    I find the solution for saving option. please follow as below mentioned steps
    i. save Excel macro-Enabled Workbook and go excel developer next Macro security next select enable all the macros. now you can enjoy the creation
    Thanks for who has create the program

    ReplyDelete
  105. hello friends

    This is really working but I need that VB script without Rupee and paises
    thank you

    ReplyDelete
  106. Hello Programmers

    You really rock. It's simply awesome to use it. I need a little favor from you. I am working in KSA. I changed the Rupee and paise in to SAR and Halala. But my team ask me to change some modification in your program. They need the result in following formats 105 = One Hundred and five riyal ONLY. They need to add the word only after every sentence. Please mail me when you got the result from the VB script. this is my mail id : jayendrajavi@gmail.com

    Thank you

    ReplyDelete
  107. how I can vonvert to AED format ?

    ReplyDelete
  108. i need this formula to Qatar riyal for example
    125,524.50
    Qatar Riyal One hundred twenty five thousand five hunded twenty four & Dirham fifty only

    Thanks
    Srikanth
    jessy12b@gmail.com

    ReplyDelete
  109. Hi!
    Just I wanna know the way of saving that work book with macro..
    You know I saved it as macro enable workbook. once it reopened I changed the cell value but result was #NAME? like that.. Advice me pls.

    ReplyDelete
  110. Good Morning Sir

    Need Help,

    After applying all your instruction above

    I have entered the amount in excel as 150000

    Actual Result is : One Hundred Fifty Thousand Rupees and No Paises

    Expected Result should be : one lakh and fifty thousand rupees only

    Please help me how to get the above expected result

    Mail id : ahamed.coolin@gmail.com.


    Thank You in advance.

    ReplyDelete
  111. Try...
    Free Excel Add-in Convert Excel cell Numbers to Words with Prefix and Suffix features.
    Download Link - http://www.xltool.in

    ReplyDelete
  112. I want the figures to look like this 5,857.35
    Saudi Riyal Five Thousand Eight Hundred Fifty Seven and 35/100 Only.

    ReplyDelete
  113. thanking you ,
    But once close the application, next time the format will not display the cell... what is do?

    pls send reply to mail id ---- arshadamad1@gmail.com

    ReplyDelete
  114. thanking you ,
    But once close the application, next time the format will not display the cell... what is do?

    pls send reply to mail id ----mujashay@gmail.com

    ReplyDelete
  115. Hi All Experts,

    I want the code which can convert 100000 into One Lac and if it is 200000 into Two lac... Please reply me code on garg.garishagrawal@gmail.com

    ReplyDelete
  116. This comment has been removed by the author.

    ReplyDelete
  117. Will it work in MS word?

    ReplyDelete
  118. its really hepful..Thank U..

    ReplyDelete
  119. it is very helpful . Thank you very much.

    I have one question :
    Can I get how to convert numbers like this that way but text which I need in Arabic.Please !!

    ReplyDelete
  120. This comment has been removed by the author.

    ReplyDelete
  121. Good Morning Sir

    Need Help,

    After applying all your instruction above

    I have entered the amount in excel as 779163.21

    Actual Result is = Seven Hundred Seventy Nine Thousand One Hundred Sixty Three Hundred Rupees and Twenty One paise

    Expected Result should be : Seven lacs Seventy Nine Thousand One Hundred Sixty Three Hundred Rupees and Twenty One paise

    Please help me how to get the above expected result

    Mail id : silvestor007@gmail.com.


    Thank You in advance.

    ReplyDelete
  122. Thanks..!
    User can also try free GWORD Multi Currency Converter to Words.
    Link - www.gwordformula.blogspot.com | www.xltool.in

    ReplyDelete
  123. HOW TO CONVERT NUMBER INTO TEXT WITHOUR RUPEES AND PAISA ONLY PURE TEXT. PLEASE REPLY ME URGENTLY ON MY E-MAIL ID I.E: nishantrathod@sjoganiexports.com

    ReplyDelete
  124. Hi
    Today i have learnt and tested and succesfully converted the numbers in to words by going through the first step
    THANKS A LOT
    C.SARAVANAN

    ReplyDelete
  125. This comment has been removed by the author.

    ReplyDelete
  126. Need Help,

    Numbers in to words
    1 to ONE
    2 to TWO
    3 to THREE
    4 to FOUR
    5 to FIVE
    6 to SIX
    7 to SEVEN
    8 TO EIGHT
    9 to NINE
    0 to ZERO
    -END-

    EMail id : mravmohankumar@gmail.com

    ReplyDelete
  127. If you need convert number to gujarati words Search " Alpesh175"

    ReplyDelete

Share your comment here..