Page 1 of 2 12 LastLast
Results 1 to 12 of 15

Thread: MS Excel (Auto convert Numeric in Words)

  1. #1
    parwan's Avatar
    parwan is offline Senior Member+
    Last Online
    8th June 2013 @ 09:56 PM
    Join Date
    05 Dec 2008
    Age
    44
    Posts
    50
    Threads
    2
    Credits
    0
    Thanked: 1

    Exclamation MS Excel (Auto convert Numeric in Words)

    Can any one help me in case if i write a number figure like 2000 it will also be converted to wording like Two Thousands Rupee only?

    Your help in this regard shall be highly appreciated.
    Thanks and Regards..

  2. #2
    Join Date
    10 Aug 2009
    Location
    ***************
    Gender
    Female
    Posts
    544
    Threads
    20
    Thanked
    26

    Default

    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.



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



    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.

  3. #3
    lovtam is offline Advance Member
    Last Online
    4th December 2021 @ 09:57 PM
    Join Date
    05 Jul 2008
    Location
    Baharoo main Nazaroo Main
    Age
    39
    Posts
    1,923
    Threads
    316
    Credits
    324
    Thanked
    15

    Default

    pta chala ye to kafi lamba process hai

  4. #4
    lovtam is offline Advance Member
    Last Online
    4th December 2021 @ 09:57 PM
    Join Date
    05 Jul 2008
    Location
    Baharoo main Nazaroo Main
    Age
    39
    Posts
    1,923
    Threads
    316
    Credits
    324
    Thanked
    15

    Default

    bhai jaan ye process ms word mein ni ho skta kya jaldi btaien

  5. #5
    parwan's Avatar
    parwan is offline Senior Member+
    Last Online
    8th June 2013 @ 09:56 PM
    Join Date
    05 Dec 2008
    Age
    44
    Posts
    50
    Threads
    2
    Credits
    0
    Thanked: 1

    Default

    thanks buddy it was really help ful..!!!

  6. #6
    waqas urdu is offline Advance Member
    Last Online
    28th January 2020 @ 04:54 PM
    Join Date
    25 Jun 2008
    Location
    Karachi
    Age
    34
    Posts
    1,150
    Threads
    126
    Credits
    145
    Thanked
    16

    Default

    very useful function

  7. #7
    naveedeng is offline Senior Member+
    Last Online
    26th March 2011 @ 02:49 PM
    Join Date
    12 Mar 2011
    Gender
    Male
    Posts
    48
    Threads
    5
    Credits
    990
    Thanked
    3

    Default

    No Idea

  8. #8
    Haseeb Alamgir's Avatar
    Haseeb Alamgir is offline Super Moderator
    Last Online
    Yesterday @ 10:43 PM
    Join Date
    09 Apr 2009
    Location
    KARACHI&
    Gender
    Male
    Posts
    19,307
    Threads
    412
    Credits
    39,753
    Thanked
    1813

    Default

    میرا زاتی خیال ہے کہ ہو سکتا ہے کیونکہ کہ میکرو کی کوڈینگ ہے اور کسی بھی میکروسوفٹ کی ایپلیکیشن میں استعمال کی جاسکتی ہے۔
    اصل مسلہ یہ آئے گا کہ یہ جس فائل میں کوڈ لکھا جائے گا یہ کام صرف اسی فائل میں ہو سکے گا۔۔۔ہربار نئی کوڈینگ کے ساتھ فائل بنانی ہوگی یا پھر سیو ایز کرنا ہوگا۔

  9. #9
    Haseeb Alamgir's Avatar
    Haseeb Alamgir is offline Super Moderator
    Last Online
    Yesterday @ 10:43 PM
    Join Date
    09 Apr 2009
    Location
    KARACHI&
    Gender
    Male
    Posts
    19,307
    Threads
    412
    Credits
    39,753
    Thanked
    1813

    Default

    ویسے اگر یہ اوپر والا کوڈ صیح کام کر تا ہے تو کاپی پیسٹ کرنے اور نئی فائل بنانے میں کوئی ہرج نہیں ہے۔ شاہد بھائی آپ نے اتنی اچھی بات ہم سب کے ساتھ شیئر کی اس کے لیے بہت بہت شکریہ

  10. #10
    IT.BOY's Avatar
    IT.BOY is offline Senior Member
    Last Online
    17th November 2017 @ 12:13 PM
    Join Date
    26 Dec 2007
    Location
    *MAA KI AGOOSH*
    Gender
    Male
    Posts
    28,374
    Threads
    651
    Credits
    998
    Thanked
    3079

    Default

    تھوڑا سا انتظار کریں آپ کے لیے ایک تھریڈ تھا وہ چیک کر رہا ہوں
    [FONT="Jameel Noori Nastaleeq"][CENTER][COLOR="Blue"][SIZE="5"][B]Coming Soon...:)[/B][/SIZE][/COLOR][/CENTER][/FONT]

  11. #11
    IT.BOY's Avatar
    IT.BOY is offline Senior Member
    Last Online
    17th November 2017 @ 12:13 PM
    Join Date
    26 Dec 2007
    Location
    *MAA KI AGOOSH*
    Gender
    Male
    Posts
    28,374
    Threads
    651
    Credits
    998
    Thanked
    3079

    Default

    چیک کرو دوست
    Code:
    =TEXT(E13, "MMMM") & " " & NBTEXT(TEXT(E13, "d")) & ", " & NBTEXT(TEXT(E13, "YYYY"))

  12. #12
    naeemgees's Avatar
    naeemgees is offline Senior Member+
    Last Online
    27th January 2015 @ 03:55 PM
    Join Date
    22 Oct 2008
    Location
    Saudi Arabia
    Age
    38
    Gender
    Male
    Posts
    108
    Threads
    14
    Credits
    965
    Thanked
    2

    Default

    shahid bahi aap ka method mere bohat kaam ka hy mene try keya hy zabardast hy lakin mujhay 1 problum hy main saudia arabia main quantity surveyor hon or mujhay yea conversion (Rupees=Riyal & Paisas=Halala) main chayea mene aap ki file main rupees ko riyals main replace ker k dekha hy lakin shayed mere sy wo sahi nahi howa keya aap issay mere leyea change ker k dy sktay hain Plz

Page 1 of 2 12 LastLast

Similar Threads

  1. How to Convert a number to English words-Excel
    By Abu Dajana in forum Urdu Tutorials & Designing
    Replies: 24
    Last Post: 31st May 2016, 09:29 AM
  2. Excel !How To Convert RS into words
    By Adeel Anjum in forum Ask an Expert
    Replies: 5
    Last Post: 18th May 2013, 12:06 PM
  3. Solved LinK CONVERT IN Desired words. .
    By asif4796299 in forum Solved Problems (IT)
    Replies: 7
    Last Post: 13th April 2013, 06:02 PM
  4. Solved convert numbers into words in excel
    By naeemgees in forum Solved Problems (IT)
    Replies: 8
    Last Post: 27th April 2011, 07:07 PM

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
  •