Why Convert Numbers to Words in Excel?
There are several reasons you might want to convert numbers to words in Excel:
- Financial Documents: Many financial documents such as checks, invoices, or receipts require that numbers be spelled out to avoid fraud or confusion.
- Currency Formatting: In countries where currencies such as the Peso are used, it’s often necessary to present amounts in both numbers and words for legal purposes.
- Improving Readability: Converting numbers to words can make your Excel spreadsheets easier to read, especially for those who might not be familiar with the formatting of numerical data.
Challenges in Converting Numbers to Words in Excel
Excel doesn’t have a built-in function to convert numbers to words directly. Therefore, you'll need to use a custom VBA function. VBA is a programming language within Excel that enables you to extend its functionality.
If you aren't familiar with VBA, don’t worry. The process of adding and using a VBA script is straightforward, and this article will guide you through it step by step.
Step-by-Step Guide to Convert Numbers to Words in Excel for Peso
Step 1: Open Excel and Enable Developer Tab
Before you start, you need to ensure that the Developer tab is enabled in Excel, as this is where you'll be working with VBA.
- Open Excel.
- Go to the "File" menu.
- Click "Options."
- In the Excel Options dialog box, select "Customize Ribbon."
- Check the box for the "Developer" tab and click "OK."
Step 2: Open Visual Basic for Applications (VBA)
Once the Developer tab is enabled:
- Click on the "Developer" tab.
- Select "Visual Basic" to open the VBA editor.
- In the VBA editor, go to "Insert" and then "Module." This will open a new module window where you can enter the code.
Step 3: Add VBA Code to Convert Numbers to Words
Copy and paste the following VBA code into the module window:
vba
Copy code
Function NumToWords(ByVal MyNumber)
Dim Units As String
Dim Tens As String
Dim Pesos As String
Dim Cents As String
Dim DecimalPlace As Integer
Dim Count As Integer
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
' Convert MyNumber to String in case it's a number.
MyNumber = Trim(CStr(MyNumber))
' Find Position of decimal place.
DecimalPlace = InStr(MyNumber, ".")
' Convert Peso amount.
If DecimalPlace > 0 Then
Pesos = Left(MyNumber, DecimalPlace - 1)
Cents = Mid(MyNumber, DecimalPlace + 1) & "/100"
Else
Pesos = MyNumber
Cents = "00/100"
End If
Count = 1
Do While Pesos <> ""
Temp = GetHundreds(Right(Pesos, 3))
If Temp <> "" Then Units = Temp & Place(Count) & Units
If Len(Pesos) > 3 Then
Pesos = Left(Pesos, Len(Pesos) - 3)
Else
Pesos = ""
End If
Count = Count + 1
Loop
NumToWords = Units & "Pesos " & Cents
End Function
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
Function GetTens(TensText)
Dim Result As String
Result = ""
If Val(Left(TensText, 1)) = 1 Then
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
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))
End If
GetTens = Result
End Function
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
This VBA code converts any number into words in the Peso format. It handles numbers up to billions and includes centavos, which are represented as "00/100" when no cents are provided.
Step 4: Save the Workbook
Before you can use the function, save your workbook as a macro-enabled file:
- Click on "File."
- Select "Save As."
- Choose "Excel Macro-Enabled Workbook" (.xlsm) from the drop-down menu.
- Save the file.
Step 5: Using the NumToWords Function in Excel
Now that you've added the VBA function, you can use it in your Excel sheet like a normal function. Here’s how:
- In a cell where you want to convert a number to words, type:
=NumToWords(A1) - Replace "A1" with the cell that contains the number you want to convert.
For example, if cell A1 contains the value 1234.56, the result would be "One Thousand Two Hundred Thirty-Four Pesos 56/100."
Conclusion
With this guide, you now know how to convert number to words in excel peso using a custom VBA function. This process is particularly useful for preparing financial documents like invoices or checks. By following these steps, you’ll be able to automatically spell out any numerical amount in Peso, complete with centavos. While Excel doesn’t offer this feature natively, the flexibility of VBA allows you to extend its functionality to meet your needs.
