Results 1 to 5 of 5

Thread: Help about Excel

  1. #1
    Join Date
    02 Jul 2007
    Location
    Wah Cantt
    Posts
    104
    Threads
    47
    Credits
    0
    Thanked
    2

    Exclamation Help about Excel

    Aoa

    Kia Excel me koi aesa formula he ke app koi number tupe karein
    us ko English text me show kare example

    5111 me type karon aur wo ye show kare five thousand one hundred and eleven.

  2. #2
    Join Date
    26 Aug 2006
    Location
    Karachi
    Age
    32
    Posts
    268
    Threads
    74
    Credits
    0
    Thanked
    4

    Default

    To convert currency into words, you'll need to open your Excel spreadsheet. Press Alt-F11 and create a new module. (Under the Insert menu, select Module).

    Then paste into the new module the following two functions:

    Code:
    Function English(ByVal N As Currency) As String
    
            Const Thousand = 1000@
            Const Million = Thousand * Thousand
            Const Billion = Thousand * Million
            Const Trillion = Thousand * Billion
    
            If (N = 0@) Then English = "zero": Exit Function
    
            Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
            Dim Frac As Currency: Frac = Abs(N - Fix(N))
            If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
            Dim AtLeastOne As Integer: AtLeastOne = N >= 1
    
            If (N >= Trillion) Then
                Debug.Print N
                Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
                N = N - Int(N / Trillion) * Trillion
                If (N >= 1@) Then Buf = Buf & " "
            End If
    
            If (N >= Billion) Then
                Debug.Print N
                Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
                N = N - Int(N / Billion) * Billion
                If (N >= 1@) Then Buf = Buf & " "
            End If
    
            If (N >= Million) Then
                Debug.Print N
                Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
                N = N Mod Million
                If (N >= 1@) Then Buf = Buf & " "
            End If
    
            If (N >= Thousand) Then
                Debug.Print N
                Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
                N = N Mod Thousand
                If (N >= 1@) Then Buf = Buf & " "
            End If
    
            If (N >= 1@) Then
                Debug.Print N
                Buf = Buf & EnglishDigitGroup(N)
            End If
    
            If (Frac = 0@) Then
                Buf = Buf & " exactly"
            ElseIf (Int(Frac * 100@) = Frac * 100@) Then
                If AtLeastOne Then Buf = Buf & " and "
                Buf = Buf & Format$(Frac * 100@, "00") & "/100"
            Else
                If AtLeastOne Then Buf = Buf & " and "
                Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
            End If
    
            English = Buf
        End Function
    
        Private Function EnglishDigitGroup(ByVal N As Integer) As String
    
            Const Hundred = " hundred"
            Const One = "one"
            Const Two = "two"
            Const Three = "three"
            Const Four = "four"
            Const Five = "five"
            Const Six = "six"
            Const Seven = "seven"
            Const Eight = "eight"
            Const Nine = "nine"
            Dim Buf As String: Buf = ""
            Dim Flag As Integer: Flag = False
    
            Select Case (N \ 100)
                Case 0: Buf = "": Flag = False
                Case 1: Buf = One & Hundred: Flag = True
                Case 2: Buf = Two & Hundred: Flag = True
                Case 3: Buf = Three & Hundred: Flag = True
                Case 4: Buf = Four & Hundred: Flag = True
                Case 5: Buf = Five & Hundred: Flag = True
                Case 6: Buf = Six & Hundred: Flag = True
                Case 7: Buf = Seven & Hundred: Flag = True
                Case 8: Buf = Eight & Hundred: Flag = True
                Case 9: Buf = Nine & Hundred: Flag = True
            End Select
    
            If (Flag <> False) Then N = N Mod 100
            If (N > 0) Then
                If (Flag <> False) Then Buf = Buf & " "
            Else
                EnglishDigitGroup = Buf
                Exit Function
            End If
    
            Select Case (N \ 10)
                Case 0, 1: Flag = False
                Case 2: Buf = Buf & "twenty": Flag = True
                Case 3: Buf = Buf & "thirty": Flag = True
                Case 4: Buf = Buf & "forty": Flag = True
                Case 5: Buf = Buf & "fifty": Flag = True
                Case 6: Buf = Buf & "sixty": Flag = True
                Case 7: Buf = Buf & "seventy": Flag = True
                Case 8: Buf = Buf & "eighty": Flag = True
                Case 9: Buf = Buf & "ninety": Flag = True
            End Select
    
            If (Flag <> False) Then N = N Mod 10
            If (N > 0) Then
                If (Flag <> False) Then Buf = Buf & "-"
            Else
                EnglishDigitGroup = Buf
                Exit Function
            End If
    
            Select Case (N)
                Case 0:
                Case 1: Buf = Buf & One
                Case 2: Buf = Buf & Two
                Case 3: Buf = Buf & Three
                Case 4: Buf = Buf & Four
                Case 5: Buf = Buf & Five
                Case 6: Buf = Buf & Six
                Case 7: Buf = Buf & Seven
                Case 8: Buf = Buf & Eight
                Case 9: Buf = Buf & Nine
                Case 10: Buf = Buf & "ten"
                Case 11: Buf = Buf & "eleven"
                Case 12: Buf = Buf & "twelve"
                Case 13: Buf = Buf & "thirteen"
                Case 14: Buf = Buf & "fourteen"
                Case 15: Buf = Buf & "fifteen"
                Case 16: Buf = Buf & "sixteen"
                Case 17: Buf = Buf & "seventeen"
                Case 18: Buf = Buf & "eighteen"
                Case 19: Buf = Buf & "nineteen"
            End Select
    
            EnglishDigitGroup = Buf
    
        End Function

    Now, when you want to convert currency into words, you will reference the English function in your Excel spreadsheet as follows:

    =English (100) would return "one hundred exactly"
    =English (125) would return "one hundred twenty-five exactly"
    =English (125.3) would return "one hundred twenty-five and 30/100"
    Signature ITDUNYA.COM hai na

    Your Borther
    Junaid

  3. #3
    Join Date
    26 Aug 2006
    Location
    Karachi
    Age
    32
    Posts
    268
    Threads
    74
    Credits
    0
    Thanked
    4

    Default

    How to create the sample function Called SpellNumber

    Start Microsoft Excel.
    Press ALT+F11 to start the Visual Basic Editor.
    On the Insert menu, click Module.
    Type the following code into the module sheet.

    Code:
    Option Explicit
        'Main Function
        Function SpellNumber(ByVal MyNumber)
            Dim Dollars, Cents, 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 cents and set MyNumber to dollar amount.
            If DecimalPlace > 0 Then
                Cents = 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 Dollars = Temp & Place(Count) & Dollars
                If Len(MyNumber) > 3 Then
                    MyNumber = Left(MyNumber, Len(MyNumber) - 3)
                Else
                    MyNumber = ""
                End If
                Count = Count + 1
            Loop
            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
            SpellNumber = Dollars & Cents
        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


    How to use the SpellNumber sample function
    To use the sample functions to change a number to written text, use one of the methods demonstrated in the following examples:

    Method 1: Direct Entry
    You can change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering the following formula into a cell:
    =SpellNumber(32.50)

    Method 2: Cell reference
    You can refer to other cells in the workbook. For example, enter the number 32.50 into cell A1, and type the following formula into another cell:
    =SpellNumber(A1)

    Method 3: Paste Function or Insert Function
    To enter a custom function into a worksheet, you can use Paste Function in Excel 2000, or you can use Insert Function in Excel 2002 and in Excel 2003.
    Excel 2000
    To use Paste Function, follow these steps:

    Select the cell that you want.
    Click Paste Function on the Standard toolbar.
    Under Function category, click User Defined.
    Under Function name, click SpellNumber, and then click OK.
    Enter the number or cell reference that you want, and then click OK.

    Excel 2002 and Excel 2003
    To use Insert Function, follow these steps:

    Select the cell that you want.
    Click Insert Function on the Standard toolbar.
    Under Or select a category, click User Defined.
    In the Select a function list, click SpellNumber, and then click OK.
    Enter the number or cell reference that you want, and then click OK.

  4. #4
    Join Date
    26 Aug 2006
    Location
    Karachi
    Age
    32
    Posts
    268
    Threads
    74
    Credits
    0
    Thanked
    4

    Default

    Problem solved please close the thread

  5. #5
    Join Date
    02 Jul 2007
    Location
    Wah Cantt
    Posts
    104
    Threads
    47
    Credits
    0
    Thanked
    2

    Default

    Thanks all for replying problem solved

Similar Threads

  1. Answered MS Word Ki file ko JPG me kesy convert kru?
    By 1heart2pain in forum Ask an Expert
    Replies: 10
    Last Post: 24th May 2010, 02:34 AM
  2. Excel 2003 for Starters: The Missing Manual
    By oralover2006 in forum English IT Zone
    Replies: 2
    Last Post: 28th May 2009, 11:01 AM
  3. MicroSoft Excel short cuts
    By khurshid akhtar in forum General Discussion
    Replies: 1
    Last Post: 17th January 2008, 02:58 PM
  4. MS Office 2000 Shortcut Keys
    By general in forum General Discussion
    Replies: 4
    Last Post: 31st May 2007, 04:07 PM
  5. Excel Shortcut Keys
    By cuticle in forum General Discussion
    Replies: 3
    Last Post: 29th January 2007, 07:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •