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..
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..
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.
pta chala ye to kafi lamba process hai
bhai jaan ye process ms word mein ni ho skta kya jaldi btaien
thanks buddy it was really help ful..!!!
very useful function
No Idea
میرا زاتی خیال ہے کہ ہو سکتا ہے کیونکہ کہ میکرو کی کوڈینگ ہے اور کسی بھی میکروسوفٹ کی ایپلیکیشن میں استعمال کی جاسکتی ہے۔
اصل مسلہ یہ آئے گا کہ یہ جس فائل میں کوڈ لکھا جائے گا یہ کام صرف اسی فائل میں ہو سکے گا۔۔۔ہربار نئی کوڈینگ کے ساتھ فائل بنانی ہوگی یا پھر سیو ایز کرنا ہوگا۔
ویسے اگر یہ اوپر والا کوڈ صیح کام کر تا ہے تو کاپی پیسٹ کرنے اور نئی فائل بنانے میں کوئی ہرج نہیں ہے۔ شاہد بھائی آپ نے اتنی اچھی بات ہم سب کے ساتھ شیئر کی اس کے لیے بہت بہت شکریہ
تھوڑا سا انتظار کریں آپ کے لیے ایک تھریڈ تھا وہ چیک کر رہا ہوں
[FONT="Jameel Noori Nastaleeq"][CENTER][COLOR="Blue"][SIZE="5"][B]Coming Soon...:)[/B][/SIZE][/COLOR][/CENTER][/FONT]
چیک کرو دوست
Code:=TEXT(E13, "MMMM") & " " & NBTEXT(TEXT(E13, "d")) & ", " & NBTEXT(TEXT(E13, "YYYY"))
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
Bookmarks