Are you tired of looking at a long list of numbers in your Excel spreadsheet? Do you wish there was an easier way to read and understand the data? Well, you’re in luck! Converting those numbers into words is a simple task that can make your Excel experience much more enjoyable.
Why Convert Numbers to Words?
You may be wondering, why bother converting numbers to words if the information is the same? Here are a few reasons why:
- Readability: Words are easier to read and understand than long strings of numbers.
- Presentation: Converting numbers to words can make your spreadsheet look more professional and polished.
- Localization: In certain countries, it is more common to use words instead of numbers, so converting could make your data more accessible.
How to Convert Numbers to Words in Excel
Now that you know why it’s important to convert numbers to words, let’s get to the how. There are a few different methods you can use to do this, so choose the one that works best for you:
Method 1: Use the SpellNumber Function
Excel actually has a built-in function that allows you to spell out a number in words. Here’s how to use it:
- Select the cell where you want the spelled-out number to appear.
- Type ‘=SpellNumber’ (without the quotes) followed by the number you want to convert in parentheses.
- Press Enter.
For example, if you wanted to spell out the number 123, you would type ‘=SpellNumber(123)’ and press Enter. The cell will then display ‘One Hundred Twenty-Three’.
Method 2: Create a Custom Function
If you find yourself frequently converting numbers to words, you may want to create a custom function to automate the process. Here’s how:
- Press Alt + F11 to open the Visual Basic Editor.
- Click Insert > Module.
- Type the following code into the module:
Function SpellNumber(ByVal MyNumber)
If MyNumber = 0 Then
SpellNumber = "Zero"
Exit Function
End If
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
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 <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
End Function
Private Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Trim(Result)
End Function
Private Function GetTens(ByVal TensText)
Dim Result As String
If 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 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 = Trim(Result)
End Function
Private Function GetDigit(ByVal 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- Save the module.
- Return to your spreadsheet and select the cell where you want the spelled-out number to appear.
- Type the following formula: ‘=SpellNumber(A1)’ (without the quotes), where ‘A1’ is the cell containing the number you want to convert.
- Press Enter.
The cell will then display the number formatted in words.
FAQ
Q: Can I convert negative numbers to words?
A: Yes, both methods outlined above can handle negative numbers.
Q: What is the maximum number that can be converted using these methods?
A: The ‘SpellNumber’ function is able to convert up to 1 quadrillion (15 digits). The custom function listed above can convert up to 999 trillion (12 digits).
Conclusion
Converting numbers to words may seem like a small task, but it can make a big difference in the readability and professionalism of your Excel spreadsheets. Whether you choose to use the built-in ‘SpellNumber’ function or create a custom function, the end result will be a clear and concise representation of your data. So why not give it a try?
Video Tutorial: Converting Numbers to Words in Excel
Further Reading
To learn more about using Excel and making the most of its features, check out these articles:
