Hi I thought converting to numbers to text using formulas much better than using macros because if you use macros:
1) You will need to enable macros 2) If you use an add-in, that formula doesn't work when you copy that file to another computer where the add-in is not installed I have prepared an excel workbook to convert numbers to text without using macros. BUT HOW DO I ATTACH that file here??!!! I dont see the "Attach" option at all :( http://sites.google.com/site/myexcelmacros/files/NumberToText.xls?attredirects=0 Copy the above URL I have uploaded that to my site On Jul 21, 11:07 am, Nandkumar kakvipure <nandkumar.hindust...@gmail.com> wrote: > Hello, > Plse tell how to work > Pls tell me step > > Thanks in advance > > On Tue, Jul 21, 2009 at 10:55 AM, Pranaya Pradhan <pradhan.pran...@gmail.com > > > > > wrote: > > TRY THIS ONE > > > Function SpellNumber(ByVal MyNumber, Optional incRupees As Boolean = True) > > Dim Crores, Lakhs, Rupees, Paise, Temp > > Dim DecimalPlace As Long, Count As Long > > Dim myLakhs, myCrores > > 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 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 > > myCrores = MyNumber \ 10000000 > > myLakhs = (MyNumber - myCrores * 10000000) \ 100000 > > MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 > > Count = 1 > > Do While myCrores <> "" > > Temp = GetHundreds(Right(myCrores, 3)) > > If Temp <> "" Then Crores = Temp & Place(Count) & Crores > > 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 Lakhs = Temp & Place(Count) & Lakhs > > 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 Crores > > > Case "": Crores = "" > > > Case "One": Crores = " One Crore " > > > Case Else: Crores = Crores & " Crores " > > > End Select > > > Select Case Lakhs > > > Case "": Lakhs = "" > > > Case "One": Lakhs = " One Lakh " > > > Case Else: Lakhs = Lakhs & " Lakhs " > > > 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 = " Paise One Only " > > > Case Else: Paise = " and Paise " & Paise & " Only " > > > End Select > > > SpellNumber = IIf(incRupees, "(Rupees ", "") & Crores & Lakhs & Rupees & > > Paise & ")" > > > 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 > > > REGARDS > > PRANAY > > > On 7/20/09, vikas gupta <vikas.63...@gmail.com> wrote: > > >> hi excel gurus > > >> can you please provide me code for convert number in words in excel > > >> regards > >> vikas- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,000 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
