SpellNumber Pakistani Rupees Function is a ready-to-use Excel function created with VBA code to change given numbers to words in Pakistani Rupees. In simple terms, it converts a numeric value of PKR to words along with paisa.
Excel doesn’t have any inbuilt function to display the numeric value in words. Microsoft provides a VBA code to display US Dollars in words.
Insert this function in your Excel and it will convert the numeric value of US Dollars to words.
For example, to display $ 2,345.50 as “Dollar Two Thousand Three Hundred Forty-Five and Fifty Cents”, you can use the SpellNumber Function.
Whereas, to display PKR 2,345.50 as ” Two Thousand Three Hundred Forty-Five Rupees and Fifty Paisa”, you can use the SpellNumber Pakistani Rupees Function. We have edited the same code for the required result.
Here is a step by step process to create this SpellNumber Function for Pakistani Rupees using the VBA module.
You can also check our SpellNumber Without Currency, SpellNumber Bangladeshi Taka and SpellNumber for Indian Rupees.
How To Create SpellNumber Pakistani Rupees Function?
Follow the below-given steps to insert the function in your excel workbook:
1. Open a new excel workbook.
2. Go To Developer Tab in the top ribbon.
3. Click on “Visual Basic” in the Code tab.
4. In the top menu, Go to Insert Tab. Select “Module” shown below:
5. Copy and paste the VBA code given-below in the Module sheet.
VBA Code for SpellNumber Function for PKR
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Option Explicit | |
| 'Main Function www.ExcelDataPro.com | |
| Function SpellNumberEDP2(ByVal MyNumber, Optional MyCurrency As String = "") | |
| Dim Dollars, Cents, Temp | |
| Dim DecimalPlace, Count, x | |
| ReDim Place(9) As String | |
| Place(2) = " Thousand " | |
| Place(3) = " Lacs " | |
| Place(4) = " Crores " | |
| Place(5) = " Hundred Crores " | |
| ' 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 <> "" | |
| If Count = 1 Then | |
| x = 3 | |
| Else | |
| x = 2 | |
| End If | |
| Temp = GetHundreds(Right(MyNumber, x)) | |
| If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars | |
| If Len(MyNumber) > x Then | |
| MyNumber = Left(MyNumber, Len(MyNumber) – x) | |
| Else | |
| MyNumber = "" | |
| End If | |
| Count = Count + 1 | |
| Loop | |
| Dim str_amount, str_amounts | |
| Dim str_cent, str_cents | |
| Select Case UCase(MyCurrency) | |
| Case "INR" | |
| str_amount = "Rupee" | |
| str_amounts = "Rupees" | |
| str_cent = "Paisa" | |
| str_cents = "Paisas" | |
| Case "PKR" | |
| str_amount = "Rupee" | |
| str_amounts = "Rupees" | |
| str_cent = "Paisa" | |
| str_cents = "Paisas" | |
| Case "BDT" | |
| str_amount = "Taka" | |
| str_amounts = "Takas" | |
| str_cent = "Poysha" | |
| str_cents = "Poysha" | |
| End Select | |
| Select Case Dollars | |
| Case "" | |
| Dollars = "No " & str_amounts | |
| Case "One" | |
| Dollars = "One " & str_amount | |
| Case Else | |
| Dollars = Dollars & " " & str_amounts | |
| End Select | |
| Select Case Cents | |
| Case "" | |
| Cents = " and No " & str_cents | |
| Case "One" | |
| Cents = " and One " & str_cent | |
| Case Else | |
| Cents = " and " & Cents & " " & str_cents | |
| End Select | |
| SpellNumberEDP2 = 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 |
Error Rectification
If the above code doesn’t run or gives the following error “COMPILE ERROR” then follow the following steps:
- Click on “view raw” below the code.
- A new window will open with the code.
- Copy that code and paste it into the VBA module instead of direct copy paste.
6. Click on the “Save” button. When you do this, a screen will display a dialog box as shown below.
Your file consists of a macro thus you need to click the “No” option.
7. Select the location and select the “Save as type” as an “Excel macro-enabled workbook”.
SpellNumber function for PKR (Pakistani Rupees) is inserted and ready to be used.
How to Use SpellNumber Pakistani Rupees Function
Let’s try the inserted SpellNumber function inserted in our excel workbook.
Insert number in column A. In column B insert the formula: = SpellNumberEDP2(A1, “PKR”). PKR is the string you need to enter in double inverted commas. Use the fill handle function to copy the formula in all rows.
The function displays the following result:
This function will only be available in this workbook. Insert again in a new workbook if you want to use it.
Please keep in mind that every time you open this workbook or any other macro-enabled workbook you will get a security warning below the ribbon. See image below:
Select “Enable this content” and click on “OK”. Otherwise, the code will not function.
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries or suggestions please share in the comment section below. I will be more than happy to assist you.
