How to Convert Numbers into Letters in Excel: A Complete Guide
Excel is a powerful tool for managing data and performing calculations, but it can be frustrating when you need to work with text. One common task is converting numbers into letters, which can be useful for invoices, financial reports, and other documents. In this article, we will show you several methods for converting numbers into letters in Excel.
Method 1: Using the SpellNumber Function
The easiest way to convert numbers into letters in Excel is to use the built-in SpellNumber function. This function is not available by default in Excel, but you can add it by copying the following code into a new module:
Function SpellNumber(ByVal MyNumber) As String
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
Count = DecimalPlace - 1
Else
Count = Len(MyNumber)
End If
If Count > 15 Then
SpellNumber = "Too Big"
Exit Function
End If
Do While Count > 0
Select Case Count
Case 1 To 3
If Left(MyNumber, Count) <> "0" Then
SpellNumber = SpellNumber & GetDigit(Left(MyNumber, Count)) & Place(Count)
End If
Case 4 To 6
If Mid(MyNumber, Count - 2, 3) <> "000" Then
SpellNumber = SpellNumber & GetDigit(Mid(MyNumber, Count - 2, 3)) & Place(Count)
End If
Case 7 To 9
If Mid(MyNumber, Count - 5, 3) <> "000" Then
SpellNumber = SpellNumber & GetDigit(Mid(MyNumber, Count - 5, 3)) & Place(Count)
End If
Case 10 To 12
If Mid(MyNumber, Count - 8, 3) <> "000" Then
SpellNumber = SpellNumber & GetDigit(Mid(MyNumber, Count - 8, 3)) & Place(Count)
End If
Case 13 To 15
If Mid(MyNumber, Count - 11, 3) <> "000" Then
SpellNumber = SpellNumber & GetDigit(Mid(MyNumber, Count - 11, 3)) & Place(Count)
End If
End Select
Count = Count - 3
Loop
If DecimalPlace > 0 Then
SpellNumber = SpellNumber & "and " & GetCents(Mid(MyNumber, DecimalPlace + 1)) & " Cents"
End If
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
Private Function GetTens(ByVal 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
Private Function GetHundreds(ByVal MyNumber)
Dim Result As String
Result = ""
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
If Left(MyNumber, 1) <> "0" Then
Result = GetDigit(Left(MyNumber, 1)) & " Hundred "
End If
If Right(MyNumber, 2) <> "00" Then
Result = Result & GetTens(Right(MyNumber, 2))
End If
GetHundreds = Result
End Function
Private Function GetCents(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then
Result = "Zero"
Else
Result = GetTens(Left(MyNumber, 2))
End If
GetCents = Result
End Function
Once you have added the SpellNumber function to your workbook, you can use it to convert numbers into letters in a cell by entering a formula like =SpellNumber(A1).
Method 2: Using Custom Number Formats
Another way to convert numbers into letters in Excel is to use custom number formats. This method is less complicated than using the SpellNumber function, but it has some limitations. For example, it can only convert numbers up to 999 trillion.
To use custom number formats, select the cell or range of cells containing the numbers you want to convert, and open the Format Cells dialog (Ctrl+1 or right-click and select Format Cells). In the Number tab, select Custom, and in the Type box, enter the following format:
"$"#.00 "Trillion";"$"#.00 "Billion";"$"#.00 "Million";"$"#.00 "Thousand";"$"#.00
This format will display numbers in the format of dollars and cents, followed by the appropriate word for the number of trillions, billions, millions, or thousands, depending on the number of digits. For example, the number 1234567.89 will be displayed as ” $1.23 Million”.
FAQ
Q: Can I convert negative numbers into letters using the SpellNumber function?
A: Yes, the SpellNumber function can handle negative numbers. Simply enter a negative number in the cell or formula, and the function will return the corresponding words with “minus” in front.
Q: Can I change the wording of the SpellNumber function?
A: Yes, you can modify the SpellNumber function to suit your needs. Simply edit the code in the module and replace the words or phrases with your own. Just be sure to test the function to make sure it still works correctly.
Video Tutorial: How to Convert Numbers into Letters in Excel
To help you understand the methods for converting numbers into letters in Excel, we have created a video tutorial that demonstrates how to use the SpellNumber function and custom number formats. Watch it below:
