open excel then press Alt+F11.
then choose module from insert menu.
then paste all code wat i have noted u in the page.
then save the visual basic editor and close
the the function is =value(1234) (for the number directly)
or value(D4) (for any cell 's value.
U r welcome
On 7/21/09, 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
>>>
>>>
>>
>>
>>
>>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
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
-~----------~----~----~----~------~----~------~--~---
