எக்செல்லில் கூடுதல் காண்பது எப்படி?

***nambi*** · 01-09-2010, 11:01 AM

பயனுள்ள பகிர்வு தோழர் பிரவீனுக்கு நன்றி! நன்முறையில் வேலை செய்கிறது....

***nambi*** · 01-09-2010, 12:11 PM

இந்த நிரலை உள்ளிட்டு பார்க்கலாம்..........ஆனால் இதில் எழுத்துக்கள் அடைப்புக்குறிக்கிடையில் வரும்...........

Code:

Option Explicit
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Lakh "
    Place(4) = " Crore "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert Paise and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        If Count <> 1 Then
            Temp = GetHundreds(Right(MyNumber, 2))
            If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
            If Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        Else
            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
        End If
        Count = Count + 1
    Loop
    Select Case Rupees
        Case ""
            Rupees = " "
        Case "One"
            Rupees = "Re One "
         Case Else
            Rupees = "Rupees " & Rupees
    End Select
    Select Case Paise
        Case ""
            Paise = " Zero Paise "
        Case "One"
            Paise = " and Paise One"
        Case Else
            Paise = "Paise " & Paise
    End Select
    If Rupees <> " " Then Paise = " and " & Paise
    SpellNumber = " [ " & Rupees & Paise & " Only ] "
End Function
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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

**ஸ்ரீதர்** · 01-09-2010, 12:19 PM

அருமையாக வேலை செய்கின்றது ..

நம்பி அண்ணா கொடுத்தது இந்திய முறைக்கும் ,

பிரவீன் ஐயா கொடுத்தது வெளிநாட்டு முறைக்கும்

உகந்ததாக இருக்கிறது..

பகிர்ந்தமைக்கு மிக்க நன்றி.

***nambi*** · 01-09-2010, 12:20 PM

இது அடைப்புக்குறியில்லாமல் முதலில் ரூப்பிஸ் என ஆரம்பித்து எழுத்தில் எண்ணிக்கை வரும்......

Code:

Option Explicit
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Lakh "
    Place(4) = " Crore "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert Paise and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        If Count <> 1 Then
            Temp = GetHundreds(Right(MyNumber, 2))
            If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
            If Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        Else
            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
        End If
        Count = Count + 1
    Loop
    Select Case Rupees
        Case ""
            Rupees = " "
        Case "One"
            Rupees = "Re One "
         Case Else
            Rupees = "Rupees " & Rupees
    End Select
    Select Case Paise
        Case ""
            Paise = " Zero Paise "
        Case "One"
            Paise = " and Paise One"
        Case Else
            Paise = "Paise " & Paise
    End Select
    If Rupees <> " " Then Paise = " and " & Paise
    SpellNumber = "  " & Rupees & Paise & " Only  "
End Function
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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

**rajesh2008** · 01-09-2010, 05:04 PM

தலைவா, நன்றி, நன்றி, நன்றி. இன்னும் ஒன்றே ஒன்றைச் செய்து கொடுத்தால் இன்னும் மகிழ்வேன். பைசா பூஜ்ஜியமாக இருந்தால் அது எழுத்தில் வரவேண்டாம் என்று மட்டும் முடிந்தால் மாற்றிக்கொடுங்களேன்,

***nambi*** · 02-09-2010, 05:42 AM

பைசா சுழியமாக இருந்தால் (பூஜ்ஜியமாக) பைசா பற்றி எழுத்தில் வராது ரூபாய் மட்டுமே வரும்..........

Code:

Option Explicit
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Lakh "
    Place(4) = " Crore "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert Paise and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        If Count <> 1 Then
            Temp = GetHundreds(Right(MyNumber, 2))
            If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
            If Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        Else
            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
        End If
        Count = Count + 1
    Loop
    Select Case Rupees
        Case ""
            Rupees = " "
        Case "One"
            Rupees = "Re One "
         Case Else
            Rupees = "Rupees " & Rupees
    End Select
    Select Case Paise
        Case ""
            Paise = " "
        Case "One"
            Paise = " and Paise One"
        Case Else
            Paise = "Paise " & Paise
    End Select
    If Rupees <> " " Then Paise = " and " & Paise
    SpellNumber = " " & Rupees & Paise & " Only  "
End Function
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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
' Edited for the use of Indian Users by Kausik Das <Kausik.Das@gmail.com>
' 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

**rajesh2008** · 03-09-2010, 03:05 AM

நண்பரே நீங்கள் தந்தது மாதிரி செய்தால் 15000/- என்பது ருபாய் பிப்டீன் தவுசன்ட் அன்ட் ஒன்லி என்று வருகிறது. பைசா சுழியமாக இருந்தால் "அன்ட்" வேண்டாமே. அதைத்தான் மாற்றித் தரக் கேட்கிறேன். நன்றி. உங்கள் உதவிக்கு.

**அன்புரசிகன்** · 03-09-2010, 05:19 AM

Originally Posted by rajesh2008

நண்பரே நீங்கள் தந்தது மாதிரி செய்தால் 15000/- என்பது ருபாய் பிப்டீன் தவுசன்ட் அன்ட் ஒன்லி என்று வருகிறது. பைசா சுழியமாக இருந்தால் "அன்ட்" வேண்டாமே. அதைத்தான் மாற்றித் தரக் கேட்கிறேன். நன்றி. உங்கள் உதவிக்கு.

இதை முயன்று பாருங்கள். நம்பி தந்ததில் சில மாற்றங்கள் ஏற்படுத்தப்பட்டுள்ளது. அவ்வளவே. நீங்களும் முயன்றிருக்கலாம்.

Code:

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Lakh "
    Place(4) = " Crore "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert Paise and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        If Count <> 1 Then
            Temp = GetHundreds(Right(MyNumber, 2))
            If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
            If Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        Else
            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
        End If
        Count = Count + 1
    Loop
    Select Case Rupees
        Case ""
            Rupees = " "
        Case "One"
            Rupees = "Re One "
         Case Else
            Rupees = "Rupees " & Rupees
    End Select
    Select Case Paise
        Case ""
            Paise = " "
        Case "One"
            Paise = " and Paise One"
        Case Else
            Paise = "Paise " & Paise
    End Select
        
    If Rupees <> " " And Paise <> " " Then
    Paise = " and " & Paise
    SpellNumber = " " & Rupees & Paise & " Only  "
    Else
        If Rupees <> " " And Paise = " " Then
        SpellNumber = " " & Rupees & " Only  "
            Else
                If Rupees = " " And Paise <> " " Then
                SpellNumber = Paise & " Only  "
                    Else
                    If Rupees = " " And Paise = " " Then
                    SpellNumber = ""
                    End If
                End If
            End If
    End If
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

இப்போது நீங்கள் எந்த இலக்கமும் கொடுக்காவிட்டால் எதுவும் வராது.

0.25 க்கு Paise Fifty Only என்று வரும்.

755.35 என்றால் Rupees Seven Hundred Fifty Five and Paise Thirty Five Only என்று வரும்.

478 ற்கு Rupees Four Hundred Seventy Eight Only என்று வரும்.

சோதித்து கூறுங்கள்.

**headhairhair** · 03-09-2010, 07:28 AM

http://www.dq.winsila.com/wp-content...ureshAddin.xla
please use this addin

The Add-In has 3 functions – INR(), REVINR() and RSWORDS().

The INR() function converts a number to the Indian Style Comma formatted currency as you can see in the snapshot. The commas are placed in the right places separating lakhs and crores. But the result that you get is in the Text Format. So you cannot directly use the result in a formula for calculations. To overcome this issue I have added another function called REVINR().

The REVINR() function simply converts the result obtained from using INR function back to the number format so that you can use it in calculations as demonstrated in snapshot.

**rajesh2008** · 03-09-2010, 03:37 PM

நண்பரே,( உங்களை எப்படிக்கூப்பிடுவது?) நீங்க தந்திருக்கும் விஷயம் ரொம்பவும் அருமையாக உள்ளது.இதுவரை எவரும் குறிப்பிடாதது.மிக்க நன்றி.எனக்கு மிகவும் உபயோகப்படும்.

அன்பு ரசிகனின் முயற்சிக்கும், உழைப்புக்கும் கூட மிக்க நன்றி.

**syedkaleel** · 06-09-2011, 12:20 AM

நன்றி

**vseenu** · 20-09-2011, 03:22 PM

மிகவும் பயனுள்ள்தாக உள்ளது. எக்ஸெல் பயன்படுத்துவோருக்கு உபயோகமாக இருக்கும் code

Thread: எக்செல்லில் கூடுதல் காண்பது எப்படி?

Thread Tools

Display

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions