Excel is one of the most popular tools used in data management and analysis. And it’s not just for number crunching. You can use Excel for a wide variety of tasks, including creating lists, organizing data, and even writing letters. However, not everyone knows how to use Excel’s functions to their fullest potential. One handy function to know is how to write numbers in words, which can be useful for invoices and other documents. In this article, we’ll show you how to write numbers in words in Excel, without the need for third-party plugins.
Excel Function for Writing Numbers in Words
Excel has a built-in function for writing numbers in words called “SpellNumber”. This function is not included in Excel’s standard functions, but it can be accessed using a Visual Basic for Applications (VBA) macro. Here’s how:
- Open your Excel document and press “Alt + F11” to open the Visual Basic Editor.
- In the editor, click on “Insert” in the menu bar and choose “Module” from the dropdown.
- Copy the following code and paste it into the module:
Function SpellNumber(ByVal MyNumber)
' This macro converts a number to text in Bahasa Indonesia for a selected range
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = "ribu "
Place(3) = "juta "
Place(4) = "milyar "
Place(5) = "triliun "
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 = "Nol Rupiah"
Case "Satu"
Dollars = "Se"
Case Else
Dollars = Dollars
End Select
Select Case Cents
Case ""
Cents = " "
Dollars = Dollars & Cents & " Rupiah"
Case "Satu"
Cents = "Satu Sen "
Dollars = Dollars & Cents & "Rupiah"
Case Else
Cents = " Sen" & GetTens(Cents) & " "
Dollars = Dollars & Cents & "Rupiah"
End Select
SpellNumber = Dollars
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)) & " ratus "
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 = Result
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 = "sepuluh "
Case 11: Result = "sebelas "
Case 12: Result = "dua belas "
Case 13: Result = "tiga belas "
Case 14: Result = "empat belas "
Case 15: Result = "lima belas "
Case 16: Result = "enam belas "
Case 17: Result = "tujuh belas "
Case 18: Result = "delapan belas "
Case 19: Result = "sembilan belas "
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2: Result = "dua puluh "
Case 3: Result = "tiga puluh "
Case 4: Result = "empat puluh "
Case 5: Result = "lima puluh "
Case 6: Result = "enam puluh "
Case 7: Result = "tujuh puluh "
Case 8: Result = "delapan puluh "
Case 9: Result = "sembilan puluh "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1))
End If
GetTens = Result
End Function
Private Function GetDigit(ByVal Digit)
Select Case Val(Digit)
Case 1: GetDigit = "satu "
Case 2: GetDigit = "dua "
Case 3: GetDigit = "tiga "
Case 4: GetDigit = "empat "
Case 5: GetDigit = "lima "
Case 6: GetDigit = "enam "
Case 7: GetDigit = "tujuh "
Case 8: GetDigit = "delapan "
Case 9: GetDigit = "sembilan "
Case Else: GetDigit = ""
End Select
End Function
- Press “F5” or “Run” to execute the macro.
- When prompted, enter the cell reference containing the number you want to convert to words.
And that’s it! The macro will convert the number in the selected cell to words.
Changing Number to Words in Excel
If you don’t want to use a macro to convert numbers to words in Excel, you can still do it using a combination of functions. This method is less convenient but still effective. Here’s how:
- Open your Excel sheet and select the cell where you want to write the number in words.
- Enter the following formula into the cell:
=IF(LEN(A1)=1,D1&A1,IF(LEN(A1)=2,D2&A1,IF(LEN(A1)=3,D3&A1,IF(LEN(A1)=4,D4&A1,IF(LEN(A1)=5,D5&A1,IF(LEN(A1)=6,D6&A1))))))
Note: The formula above is for Indonesian language. If you are using a different language, use the corresponding words for each number.
Making Letters in Excel Bold and Underlined
If you want to make the words in the Excel sheet bold and underlined, you can do that using the following steps:
- Select the cell with the words you want to make bold and underlined.
- Click on the “Home” tab in the menu bar.
- Click on the “Font” group and choose “Bold” and “Underline” from the dropdown menu.
Adding Images to Your Excel Sheet
You might want to include images in your Excel sheet to complement the information you’ve written. Here’s how to add images:
- Right-click on the cell where you want to insert the image.
- Choose “Insert” from the dropdown menu.
- Click on the “Pictures” option.
- Choose the image you want to insert and click “Insert”.
Creating an FAQ Section in Your Excel Sheet
Another way to provide information in your Excel sheet is through a Frequently Asked Questions (FAQ) section. Here’s how:
- Insert a new sheet into your Excel file by clicking on the plus sign “+” at the bottom of the screen.
- Click on cell A1 and type “FAQ”.
- Click on cell B1 and type “Question”.
- Click on cell C1 and type “Answer”.
- Under “Question”, type in a frequently asked question.
- Under “Answer”, type in the corresponding answer.
- Repeat steps 5-6 to add more questions and answers.
YouTube Video
In addition to text and images, you can also embed a YouTube video in your Excel sheet. Here’s how:
- Find the YouTube video you want to embed and copy the URL.
- In Excel, go to the “Insert” tab and click on “Video”.
- Choose “Online Video” from the dropdown menu.
- Paste the YouTube video URL into the “From a Video Embed Code” field and click “Insert”.
FAQs
1. Can I use this function for numbers larger than six digits?
Yes, you can use this function for numbers of any size. However, this function is easier to use for six-digit numbers or smaller.
2. How do I format the cells to fit the text?
To format the cells to fit the text, click on the cell you want to format. Go to the “Home” tab in the menu bar and click on “Format”. Choose “AutoFit Row Height” to adjust the height of the row to fit the text.
Conclusion
Writing numbers in words can be a useful skill for managing data in Excel and creating professional documents. Now that you know how to write numbers in words in Excel, you can use this knowledge to enhance your documents and make them more readable.
