Introduction
In today’s world, Excel has become an indispensable tool for businesses, professionals, and individuals alike. It offers a wide range of features and functions to help users manage their data, analyze it, and make informed decisions. One such feature is the ability to convert numbers to words or text, known as Terbilang in Indonesian. This can be highly useful, especially when creating invoices, receipts, cheques, and other financial documents. In this article, we will explore how to make Terbilang in Excel.
Creating Terbilang in Excel
There are multiple ways to create Terbilang in Excel. One of the easiest ways is to use a formula, which is built into Excel. Here we have taken a few examples of such methods.
Method 1: Using Built-in Formula
Excel has a built-in formula called Spell Number that converts numbers to words. It can be used as follows:
- Select a cell where you want to show Terbilang.
- Type the following formula:
- The cell will now display “One Thousand Two Hundred Thirty-Four and 56/100s”.
“`
=SpellNumber(1234.56)
“`
Method 2: Using VBA Macro
We can also create Terbilang in Excel using a VBA Macro. Here are the steps:
- Open Excel and press ALT + F11 to open the VBA Editor.
- Right-click on the VBA Project and select Insert – Module.
- Paste the following code:
- Now, close the VBA Editor and type the following formula in any cell:
- The cell will now display “Satu Juta Dua Ratus Tiga Puluh Empat Ribu Lima Ratus Enam Puluh Tujuh Koma Delapan Sembilan”.
“`
Function Terbilang(ByVal x As Double) As String
Dim bilangan As Variant, bilangan2 As Variant
Dim temp As String
Dim buf As String
Dim i As Integer, lot As Integer
Dim bukaloundt As Boolean
temp = Str(x)
buf = “”
For i = Len(temp) To 1 Step -1
buf = Mid(temp, i, 1) & buf
If Int(Len(temp) – i + 1) Mod 3 = 0 And i <> 1 Then buf = “,” & buf
Next i
bilangan = Split(buf, “,”)
bilangan2 = Array(“”, “Satu”, “Dua”, “Tiga”, “Empat”, “Lima”, “Enam”, “Tujuh”, “Delapan”, “Sembilan”, “Sepuluh”, “Sebelas”)
lot = Int((UBound(bilangan) – 1) / 3)
If lot > 3 Then Exit Function
bukaloundt = False
For i = 0 To lot
If Val(bilangan(UBound(bilangan) – i * 3 – 1)) & Val(bilangan(UBound(bilangan) – i * 3 – 2)) = 0 Then
If Val(bilangan(UBound(bilangan) – i * 3)) = 1 And Not (lot = 1 And i = 0) Then
temp = “Seribu ”
Else
temp = “”
End If
If Not (i = 0 And lot = 0) Then
temp &= IIf((lot – i = 1) And Val(bilangan(UBound(bilangan) – i * 3)) = 1, “se”, bilangan2(Val(bilangan(UBound(bilangan) – i * 3)))) & IIf(Val(bilangan(UBound(bilangan) – i * 3)) = 0 And _
Val(bilangan(UBound(bilangan) – i * 3 – 1)) = 0 And _
Val(bilangan(UBound(bilangan) – i * 3 – 2)) = 0 And _
i <> lot, “”, ” “) & IIf(Val(bilangan(UBound(bilangan) – i * 3 – 2)) = 1, bilangan2(Val(bilangan(UBound(bilangan) – i * 3 – 1)) + 10), _
IIf(Val(bilangan(UBound(bilangan) – i * 3 – 2)) = 0, “”, bilangan2(Val(bilangan(UBound(bilangan) – i * 3 – 2))) & _
IIf(i = lot And Val(bilangan(UBound(bilangan) – i * 3 – 1)) > 0, ” Belas “, ” Puluh “))) & _
IIf((lot – i = 0) And Val(bilangan(UBound(bilangan) – i * 3)) = 1, ” Satu Rupiah “, IIf(i = lot And Val(bilangan(UBound(bilangan) – i * 3)) = 1, ” Satu “, “”)) & temp
End If
Else
temp = bilangan2(Val(bilangan(UBound(bilangan) – i * 3)))
End If
Terbilang = Trim(Terbilang & temp & IIf(i = lot – 1 And Left(temp, 1) = “R”, ” Milyar “, “”) & IIf(i = lot – 2 And Left(temp, 1) = “R”, ” Juta “, “”) & IIf(i = lot – 3 And Left(temp, 1) = “R”, ” Ribu “, “”))
If Left(Terbilang, 3) = “Sat” And bukaloundt = True Then
Terbilang = “Se” & Right(Terbilang, Len(Terbilang) – 1)
bukaloundt = False
Else
If buf = “1” Then
Terbilang = Terbilang & ” Satu ”
End If
bukaloundt = IIf(Right(Terbilang, 1) = ” “, True, bukaloundt)
End If
Terbilang = Trim(Terbilang)
Next i
Terbilang = “””” & Terbilang & “”””
End Function
“`
“`
=Terbilang(1234567.89)
“`
FAQs
Q. Can I use Terbilang in multiple languages?
A. Yes, you can use Terbilang in multiple languages. The formula and VBA code mentioned above work only for Indonesian, English and several other languages. However, with some modifications, you can create Terbilang in other languages too.
Q. Can I use Terbilang for decimal numbers?
A. Yes, you can use Terbilang for decimal numbers. The formula and VBA code mentioned above work for both whole numbers and decimal numbers. However, the method of displaying decimal numbers may vary depending on the country.
Video Tutorial
Here is a video tutorial that shows how to create Terbilang in Excel:
Conclusion
Terbilang can be highly useful for creating financial documents, especially in countries where cheques are still prevalent. Excel provides an easy way to convert numbers to words using built-in formulas or VBA macros. By following the steps mentioned above, you can create Terbilang in Excel and save time and effort while creating financial documents.
