Microsoft Excel is a powerful tool for data management and calculation, but it doesn’t natively include a function to convert numbers to words. This capability is crucial for many applications, such as generating invoices, writing checks, or preparing legal documents where amounts often need to be spelled out for clarity and accuracy. Manually typing out numbers in words can be time-consuming and prone to errors, especially with large datasets. Fortunately, there are several effective methods you can employ to convert numbers to words in Excel, significantly enhancing the functionality and professionalism of your spreadsheets.
Why Convert Numbers to Words in Excel?
The need to convert numbers to words in Excel arises in various professional and personal contexts. Understanding the primary reasons can help you appreciate the value of implementing this functionality in your work.
Enhanced Clarity: Spelling out numbers reduces ambiguity, especially for large figures or decimal values that might be misread.
Financial Accuracy: For invoices, receipts, and checks, writing amounts in words serves as a crucial verification step, preventing discrepancies and fraud.
Legal Compliance: Many legal and financial documents require monetary amounts to be stated in both numerical and word formats to ensure contractual clarity.
Professionalism: Presenting data with numbers converted to words adds a layer of professionalism and attention to detail to your reports and documents.
Method 1: Using a Custom VBA Function (SpellNumber)
The most common and robust way to convert numbers to words in Excel is by creating a Custom VBA (Visual Basic for Applications) function. This function, often referred to as ‘SpellNumber’, can be added to your workbook and then used just like any other Excel function.
Step-by-Step Guide to Implement SpellNumber
Follow these instructions carefully to add the SpellNumber function to your Excel workbook.
Open the VBA Editor: Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
Insert a New Module: In the VBA editor, go to the Insert menu and select Module. A new, blank module window will appear.
Paste the VBA Code: Copy the following VBA code and paste it into the new module window. This code defines the ‘SpellNumber’ function.
Function SpellNumber(ByVal MyNumber)Dim Dollars, Cents, Temp As StringDim DecimalPlace, Count As VariantReDim Place(9) As StringPlace(2) = " Thousand"Place(3) = " Million"Place(4) = " Billion"Place(5) = " Trillion"' String representation of amount.MyNumber = Trim(Str(MyNumber))' Position of decimal point 'DP'.DecimalPlace = InStr(MyNumber, ".")' If no decimal then add one.If DecimalPlace = 0 ThenMyNumber = MyNumber & ".00"DecimalPlace = InStr(MyNumber, ".")End If' Separate dollars and cents.Dollars = Left(MyNumber, DecimalPlace - 1)Cents = Mid(MyNumber, DecimalPlace + 1)' Convert cents and set fractional string with cents.If CInt(Cents) > 0 ThenCents = " and " & SpellNumber_Convert(Cents) & " Cents"ElseCents = ""End IfDollars = SpellNumber_Convert(Dollars)If Dollars = "" ThenSpellNumber = "No Dollars" & CentsElseSpellNumber = Dollars & CentsEnd IfEnd Function' Converts a number from 1-999 in to words.Function SpellNumber_Convert(ByVal MyNumber)Dim Hundreds, Tens, Units, Temp As StringDim Count As IntegerDim NumPlace(9) As StringDim OnePlace(19) As StringDim TenPlace(9) As StringOnePlace(1) = "One"OnePlace(2) = "Two"OnePlace(3) = "Three"OnePlace(4) = "Four"OnePlace(5) = "Five"OnePlace(6) = "Six"OnePlace(7) = "Seven"OnePlace(8) = "Eight"OnePlace(9) = "Nine"OnePlace(10) = "Ten"OnePlace(11) = "Eleven"OnePlace(12) = "Twelve"OnePlace(13) = "Thirteen"OnePlace(14) = "Fourteen"OnePlace(15) = "Fifteen"OnePlace(16) = "Sixteen"OnePlace(17) = "Seventeen"OnePlace(18) = "Eighteen"OnePlace(19) = "Nineteen"TenPlace(2) = "Twenty"TenPlace(3) = "Thirty"TenPlace(4) = "Forty"TenPlace(5) = "Fifty"TenPlace(6) = "Sixty"TenPlace(7) = "Seventy"TenPlace(8) = "Eighty"TenPlace(9) = "Ninety"Temp = ""If Val(MyNumber) = 0 ThenExit FunctionHundreds = Int(Val(MyNumber) / 100)If Hundreds > 0 ThenTemp = OnePlace(Hundreds) & " Hundred"Tens = Val(Right(MyNumber, 2))If Tens > 0 ThenIf Tens < 20 ThenTemp = Temp & " " & OnePlace(Tens)ElseUnits = Right(Tens, 1)Tens = Int(Tens / 10)Temp = Temp & " " & TenPlace(Tens)If Units > 0 ThenTemp = Temp & " " & OnePlace(Units)End IfEnd IfEnd IfSpellNumber_Convert = Trim(Temp)End Function
Close the VBA Editor: After pasting the code, you can close the VBA editor window.
Use the Function in Excel: Now, in any cell in your workbook, you can use the SpellNumber function. For example, if you have a number in cell A1, you would type =SpellNumber(A1) into another cell to see its word equivalent.
Save Your Workbook: To ensure your custom function is saved with your file, you must save the Excel workbook as an Excel Macro-Enabled Workbook (.xlsm).
This method is highly customizable and can be modified to suit specific regional number-to-word conventions (e.g., adding ‘and’ differently, handling specific currencies).
Method 2: Using an Excel Add-in
For users who prefer not to delve into VBA code, or who need a ready-to-use solution, several Excel add-ins are available that provide number-to-word conversion functionality. These add-ins typically install a new function directly into Excel, making it accessible like any built-in function.
Finding and Installing an Add-in
While Excel doesn’t have an official Microsoft add-in for this, third-party developers offer solutions.
Search for Add-ins: You can search online for “Excel number to words add-in” to find various options. Many are free, while some might be paid.
Download the Add-in: Once you find a suitable add-in, download its file (often an .xlam or .xla file).
Install the Add-in in Excel:
Go to File > Options > Add-ins.
In the ‘Manage’ dropdown, select Excel Add-ins and click Go….
Click Browse… and navigate to the location where you downloaded the add-in file.
Select the file and click OK. Ensure the checkbox next to the add-in’s name is selected in the Add-ins dialog box, then click OK again.
Use the New Function: The add-in will typically introduce a new function (e.g., =NUMBERTOWORDS, =SPELLAMOUNT) that you can use in your worksheets just like the custom VBA function.
Add-ins offer convenience and often come with additional features or different language support. However, always ensure you download add-ins from reputable sources to avoid security risks.
Method 3: Online Converters (For Static Data)
If your need to convert numbers to words in Excel is infrequent or for static data that doesn’t require dynamic updates within Excel, an online number-to-word converter can be a quick solution.
How to Use Online Converters
Access an Online Tool: Search for “number to words converter online” in your web browser.
Input Your Number: Type or paste the number you wish to convert into the converter’s input field.
Copy the Result: The tool will instantly display the number in words. Copy this text.
Paste into Excel: Paste the converted text into the desired cell in your Excel spreadsheet.
This method is simple but lacks the automation and dynamic updating capabilities of VBA functions or add-ins. It’s best suited for one-off conversions or small batches of data.
Tips for Working with Converted Numbers to Words
When you convert numbers to words in Excel, consider these tips for optimal results and usability.
Currency Customization: If you’re dealing with specific currencies (e.g., Euros, Pounds), you might need to modify the VBA code or choose an add-in that supports these denominations. The provided SpellNumber function can be adapted to include currency names like “Dollars” and “Cents.”
Error Handling: Ensure your function or add-in handles edge cases like zero, negative numbers, or extremely large numbers gracefully. The provided SpellNumber function handles zero and positive numbers effectively.
Workbook Security: If using VBA, remember that macro-enabled workbooks (.xlsm) might trigger security warnings for recipients. Inform users to enable macros if they need the functionality.
Readability: For very large numbers, the word representation can become quite long. Consider formatting the cell to wrap text for better readability within your spreadsheet.
Conclusion
Converting numbers to words in Excel is a valuable skill that enhances the clarity, accuracy, and professionalism of your financial and administrative documents. Whether you opt for the robust and customizable VBA custom function, the convenience of a third-party add-in, or the simplicity of an online converter, each method offers a practical solution to this common challenge. By implementing one of these techniques, you can streamline your workflow and ensure your numerical data is always presented in its most understandable and verifiable form. Choose the method that best suits your technical comfort level and the specific requirements of your Excel projects to effectively convert numbers to words in Excel, making your spreadsheets more powerful and user-friendly.