Sebelum posting ini, mungkin sudah banyak para pakar excel yang melakukan kreatifitas dalam membuat fungsi terbilang dengan macro maupun rumus terbilang yang dibuat murni menggunakan formula excel tanpa sentuhan macro.
Rumus terbilang ini memang begitu panjang dan panjang, karena tidak menggunakan array dan tabel sebagai alat bantu untuk merubah angka menjadi huruf.
Rumus yang dibuat ini untuk angka/nominal antara 1 sampai dengan 1.000.000.000.000.000 (1Bilyun), sedangkan untuk desimal belum bisa digunakan.
Karena rumus terbilang ini tidak menggunakan macro dan alat bantu lainnya, maka perlu beberapa fungsi yang digunakan untuk menghasilkan huruf sebagai terbilang dari angka yang diinginkan. Fungsi-fungsi tersebut diantaranya:
1. ISBLANK, digunakan untuk memastikan cell yang dimaksud adalah kosong (tak bernilai).
2. SUBSTITUTE, digunakan untuk mengganti teks lama menjadi teks baru.
3. CONCATENATE, digunakan untuk menggabungkan beberapa teks.
4. TEXT, digunakan untuk merubah nilai numerik menjadi data string (teks).
5. LEFT, digunakan untuk mengambil karakter dari sebelah kiri.
6. RIGHT, digunakan untuk mengambil karakter dari sebelah kanan.
7. AND, digunakan untuk mengetahui dua atau lebih kondisi dalam keadaan benar.
8. LEN, digunakan untuk mengetahui jumlah panjang karakter.
9. IF, digunakan untuk menentukan satu atau lebih syarat atau kondisi.
Contoh: Pada kolom B5 nominalnya sebesar Rp 999.999.999.999.999,00 agar dapat terbaca dengan huruf ada beberapa langkah yang harus dilakukan:
1. IF(ISBLANK(B5),"",...
Jika kolom B5 kosong, maka hasilnya kosong, bila bernilai lanjut ke rumus selanjutnya.
Karena Kolom B5 bernilai, lanjut ke rumus berikutnya.
2. ...IF(LEN(TEXT(B5,"0.00"))>18,LEFT(RIGHT(TEXT(B5,"0.00"),19),1),""),
IF(LEN(TEXT(B5,"0.00"))>18,"Bilyun ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 18 karakter, maka ambil karakter sebanyak 19 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 18 karakter, tampilkan Bilyun, bila tidak kosongkan...
Karena pada kolom B5 total karakternya hanya 18, tidak lebih dari 18 maka hasilnya kosong.
Hasil IF ke-1 menghasilkan kosong
3. ...IF(LEN(TEXT(B5,"0.00"))>17,LEFT(RIGHT(TEXT(B5,"0.00"),18),1),""),
IF(LEN(TEXT(B5,"0.00"))>17,"Ratus","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 17 karakter, maka ambil karakter sebanyak 18 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 17 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 18 artinya lebih dari 17, maka diambil karakter ke-18 hasilnya 9.
Kolom B5 total karakternya ada 18 artinya lebih dari 17, maka tampilkan Ratus.
Hasil IF ke-2 menghasilkan 9Ratus.
4. ...IF(LEN(TEXT(B5,"0.00"))>16,LEFT(RIGHT(TEXT(B5,"0.00"),17),1),""),
IF(LEN(TEXT(B5,"0.00"))>16,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 16 karakter, maka ambil karakter sebanyak 17 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 16 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 17 artinya lebih dari 16, maka diambil karakter ke-17 hasilnya 9.
Kolom B5 total karakternya ada 17 artinya lebih dari 16, maka tampilkan Puluh.
Hasil IF ke-3 menghasilkan 9Puluh.
5. ...IF(LEN(TEXT(B5,"0.00"))>15,LEFT(RIGHT(TEXT(B5,"0.00"),16),1),""),
IF(LEN(TEXT(B5,"0.00"))>15,IF(AND(LEFT(RIGHT(TEXT(B5,"0.00"),16),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),17),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),18),1)="0"),"","Triliun "),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 15 karakter, maka ambil karakter sebanyak 16 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 15 karakter, dan jika karakter ke-16 = "0", karakter ke-17 = "0", karakter ke-18 = "0", hasilnya dikosongkan, kemudian tampilkan Triliun, bila tidak kosongkan...
Kolom B5 total karakternya ada 16 artinya lebih dari 15, maka diambil karakter ke-16 hasilnya 9.
Kolom B5 karakter ke-16, 17 dan 18 <> "0", dan
Kolom B5 total karakternya ada 16 artinya lebih dari 15, maka tampilkan Triliun.
Hasil IF ke-4 menghasilkan 9Triliun.
6. ...IF(LEN(TEXT(B5,"0.00"))>14,LEFT(RIGHT(TEXT(B5,"0.00"),15),1),""),
IF(LEN(TEXT(B5,"0.00"))>14,"Ratus ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 14 karakter, maka ambil karakter sebanyak 15 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 14 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 15 artinya lebih dari 14, maka diambil karakter ke-15 hasilnya 9.
Kolom B5 total karakternya ada 15 artinya lebih dari 14, maka tampilkan Ratus.
Hasil IF ke-5 menghasilkan 9Ratus.
7. ...IF(LEN(TEXT(B5,"0.00"))>13,LEFT(RIGHT(TEXT(B5,"0.00"),14),1),""),
IF(LEN(TEXT(B5,"0.00"))>13,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 13 karakter, maka ambil karakter sebanyak 14 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 13 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 14 artinya lebih dari 13, maka diambil karakter ke-14 hasilnya 9.
Kolom B5 total karakternya ada 14 artinya lebih dari 13, maka tampilkan Puluh.
Hasil IF ke-6 menghasilkan 9Puluh.
8. ...IF(LEN(TEXT(B5,"0.00"))>12,LEFT(RIGHT(TEXT(B5,"0.00"),13),1),""),
IF(LEN(TEXT(B5,"0.00"))>12,IF(AND(LEFT(RIGHT(TEXT(B5,"0.00"),13),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),14),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),15),1)="0"),"","Milyar "),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 12 karakter, maka ambil karakter sebanyak 13 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 12 karakter, dan jika karakter ke-13 = "0", karakter ke-14 = "0", karakter ke-15 = "0", hasilnya dikosongkan, kemudian tampilkan Milyar, bila tidak kosongkan...
Kolom B5 total karakternya ada 13 artinya lebih dari 12, maka diambil karakter ke-13 hasilnya 9.
Kolom B5 karakter ke-13, 14 dan 15 <> "0", dan
Kolom B5 total karakternya ada 13 artinya lebih dari 12, maka tampilkan Milyar.
Hasil IF ke-7 menghasilkan 9Milyar.
9. ...IF(LEN(TEXT(B5,"0.00"))>11,LEFT(RIGHT(TEXT(B5,"0.00"),12),1),""),
IF(LEN(TEXT(B5,"0.00"))>11,"Ratus ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 11 karakter, maka ambil karakter sebanyak 12 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 11 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 12 artinya lebih dari 11, maka diambil karakter ke-12 hasilnya 9.
Kolom B5 total karakternya ada 12 artinya lebih dari 11, maka tampilkan Ratus.
Hasil IF ke-8 menghasilkan 9Ratus.
10. ...IF(LEN(TEXT(B5,"0.00"))>10,LEFT(RIGHT(TEXT(B5,"0.00"),11),1),""),
IF(LEN(TEXT(B5,"0.00"))>10,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 10 karakter, maka ambil karakter sebanyak 11 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 10 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 11 artinya lebih dari 10, maka diambil karakter ke-11 hasilnya 9.
Kolom B5 total karakternya ada 11 artinya lebih dari 10, maka tampilkan Puluh.
Hasil IF ke-9 menghasilkan 9Puluh.
11. ...IF(LEN(TEXT(B5,"0.00"))>9,LEFT(RIGHT(TEXT(B5,"0.00"),10),1),""),
IF(LEN(TEXT(B5,"0.00"))>9,IF(AND(LEFT(RIGHT(TEXT(B5,"0.00"),10),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),11),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),12),1)="0"),"","Juta "),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 9 karakter, maka ambil karakter sebanyak 10 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 9 karakter, dan jika karakter ke-10 = "0", karakter ke-11 = "0", karakter ke-12 = "0", hasilnya dikosongkan, kemudian tampilkan Juta, bila tidak kosongkan...
Kolom B5 total karakternya ada 10 artinya lebih dari 9, maka diambil karakter ke-10 hasilnya 9.
Kolom B5 karakter ke-10, 11 dan 12 <> "0", dan
Kolom B5 total karakternya ada 10 artinya lebih dari 9, maka tampilkan Juta.
Hasil IF ke-10 menghasilkan 9Juta.
12. ...IF(LEN(TEXT(B5,"0.00"))>8,LEFT(RIGHT(TEXT(B5,"0.00"),9),1),""),
IF(LEN(TEXT(B5,"0.00"))>8,"Ratus ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 8 karakter, maka ambil karakter sebanyak 9 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 8 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 9 artinya lebih dari 8, maka diambil karakter ke-9 hasilnya 9.
Kolom B5 total karakternya ada 9 artinya lebih dari 8, maka tampilkan Ratus.
Hasil IF ke-11 menghasilkan 9Ratus.
13. ...IF(LEN(TEXT(B5,"0.00"))>7,LEFT(RIGHT(TEXT(B5,"0.00"),8),1),""),
IF(LEN(TEXT(B5,"0.00"))>7,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 7 karakter, maka ambil karakter sebanyak 8 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 7 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 8 artinya lebih dari 7, maka diambil karakter ke-8 hasilnya 9.
Kolom B5 total karakternya ada 8 artinya lebih dari 7, maka tampilkan Puluh.
Hasil IF ke-12 menghasilkan 9Puluh.
14. ...IF(LEN(TEXT(B5,"0.00"))>6,LEFT(RIGHT(TEXT(B5,"0.00"),7),1),""),
IF(LEN(TEXT(B5,"0.00"))>6,IF(AND(LEFT(RIGHT(TEXT(B5,"0.00"),7),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),8),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),9),1)="0"),"","Ribu "),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 6 karakter, maka ambil karakter sebanyak 7 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 6 karakter, dan jika karakter ke-7 = "0", karakter ke-8 = "0", karakter ke-9 = "0", hasilnya dikosongkan, kemudian tampilkan Ribu, bila tidak kosongkan...
Kolom B5 total karakternya ada 7 artinya lebih dari 6, maka diambil karakter ke-7 hasilnya 9.
Kolom B5 karakter ke-7, 8 dan 9 <> "0", dan
Kolom B5 total karakternya ada 7 artinya lebih dari 6, maka tampilkan Ribu.
Hasil IF ke-13 menghasilkan 9Ribu.
15. ...IF(LEN(TEXT(B5,"0.00"))>5,LEFT(RIGHT(TEXT(B5,"0.00"),6),1),""),
IF(LEN(TEXT(B5,"0.00"))>5,"Ratus ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 5 karakter, maka ambil karakter sebanyak 6 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 5 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 6 artinya lebih dari 5, maka diambil karakter ke-6 hasilnya 9.
Kolom B5 total karakternya ada 6 artinya lebih dari 5, maka tampilkan Ratus.
Hasil IF ke-14 menghasilkan 9Ratus.
16. ...IF(LEN(TEXT(B5,"0.00"))>4,LEFT(RIGHT(TEXT(B5,"0.00"),5),1),""),
IF(LEN(TEXT(B5,"0.00"))>4,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 4 karakter, maka ambil karakter sebanyak 5 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 4 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 5 artinya lebih dari 4, maka diambil karakter ke-5 hasilnya 9.
Kolom B5 total karakternya ada 5 artinya lebih dari 4, maka tampilkan Puluh.
Hasil IF ke-15 menghasilkan 9Puluh.
17. ...IF(LEN(TEXT(B5,"0.00"))>3,LEFT(RIGHT(TEXT(B5,"0.00"),4),1),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 3 karakter, maka ambil karakter sebanyak 4 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan...
Kolom B5 total karakternya ada 4 artinya lebih dari 3, maka diambil karakter ke-4 hasilnya 9.
Hasil IF ke-16 menghasilkan 9.
Dari rumus diperoleh hasil:
IF ke-1 menghasilkan kosong (Diabaikan)
IF ke-2 menghasilkan 9Ratus.
IF ke-3 menghasilkan 9Puluh.
IF ke-4 menghasilkan 9Triliun.
IF ke-5 menghasilkan 9Ratus.
IF ke-6 menghasilkan 9Puluh.
IF ke-7 menghasilkan 9Milyar.
IF ke-8 menghasilkan 9Ratus.
IF ke-9 menghasilkan 9Puluh.
IF ke-10 menghasilkan 9Juta.
IF ke-11 menghasilkan 9Ratus.
IF ke-12 menghasilkan 9Puluh.
IF ke-13 menghasilkan 9Ribu.
IF ke-14 menghasilkan 9Ratus.
IF ke-15 menghasilkan 9Puluh.
IF ke-16 menghasilkan 9.
Apabila hasil rumus dari IF ke-1 s/d IF ke-17 digabungkan dengan fungsi CONCATENATE, karena hasil yang akan digabungkan ada 16 teks, maka penulisan fungsi CONCATENATE juga sebanyak 16, penulisannya CONCATENATE(CONCATENATE(.... sebanyak 16x.
Dari hasil penggabungan akan menghasilkan kalimat:
" 9Ratus9Puluh9Triliun9Ratus9Puluh9Milyar9Ratus9Puluh9Juta9Ratus9Puluh9Ribu9Ratus
9Puluh9 ".
Kalimat tersebut masih belum sempurna, masih dalam bentuk asli hasil pembacaan dari kolaborasi rumus. Masih berwujud angka, dari kata ke kata juga masih rapat belum terdapat spasi sebagai pemisah antar kata.
Untuk menyempurnakan hasil pembacaan rumus tersebut, perlu dilakukan penggantian terhadap teks yang dihasilkan oleh rumus. Oleh karena itu, diperlukan fungsi SUBSTITUTE untuk mengganti teks yang lebih baik dan rapi apabila dibaca.
Teks yang perlu dilakukan perubahan atau penggantian ada 30 teks, yaitu:
"0";"Nol");"1";"Satu ");"2";"Dua ");"3";"Tiga ");"4";"Empat ");"5";"Lima ");"6";"Enam ");"7";"Tujuh ");"8";"Delapan ");"9";"Sembilan ");"Satu Puluh";"Sepuluh");"Sepuluh Satu";"Sebelas");"Sepuluh Dua";"Dua Belas");"Sepuluh Tiga";"Tiga Belas");"Sepuluh Empat";"Empat Belas");"Sepuluh Lima";"Lima Belas");"Sepuluh Enam";"Enam Belas");"Sepuluh Tujuh";"Tujuh Belas");"Sepuluh Delapan";"Delapan Belas");"Sepuluh Sembilan";"Sembilan Belas");"NolTriliun";"Triliun");"NolMilyar";"Milyar");"NolJuta";"Juta");" Nol Ribu";" Ribu");"NolRatus";"");"NolPuluh";"");"Satu Ribu";"Seribu ");"Satu Ratus";"Seratus ");" Nol";" ");" Seribu";" Satu Ribu")))&" Rupiah"
Sama halnya seperti fungsi CONCATENATE, penulisan fungsi SUBSTITUTE juga sebanyak teks yang akan dilakukan penggantiannya, karena terdapat 29 teks yang dirubah atau diganti, maka penulisannya SUBSTITUTE(SUBSTITUTE(... sebanyak 29x.
Sehingga hasil dari perubahan atau penggantian teks hasil pembacaan rumus dengan teks yang baru seperti yang dibuatkan aturannya (parameternya), contoh teks "9" diganti jadi "Sembilan ", dan seterusnya, menghasilkan kalimat yang sempurna:
"Sembilan Ratus Sembilan Puluh Sembilan Triliun Sembilan Ratus Sembilan Puluh Sembilan Milyar Sembilan Ratus Sembilan Puluh Sembilan Juta Sembilan Ratus Sembilan Puluh Sembilan Ribu Sembilan Ratus Sembilan Puluh Sembilan Rupiah".
Rumus lengkapnya:
=TRIM(IF(ISBLANK(B3);"Kolom B3 Kosong";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(IF(LEN(TEXT(B3;"0.00"))>18;LEFT(RIGHT(TEXT(B3;"0.00");19);1);"");IF(LEN(TEXT(B3;"0.00"))>18;"Bilyun ";""));IF(LEN(TEXT(B3;"0.00"))>17;LEFT(RIGHT(TEXT(B3;"0.00");18);1);"");IF(LEN(TEXT(B3;"0.00"))>17;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>16;LEFT(RIGHT(TEXT(B3;"0.00");17);1);"");IF(LEN(TEXT(B3;"0.00"))>16;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>15;LEFT(RIGHT(TEXT(B3;"0.00");16);1);"");IF(LEN(TEXT(B3;"0.00"))>15;IF(AND(LEFT(RIGHT(TEXT(B3;"0.00");16);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");17);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");18);1)="0");"";"Triliun ");""));IF(LEN(TEXT(B3;"0.00"))>14;LEFT(RIGHT(TEXT(B3;"0.00");15);1);"");IF(LEN(TEXT(B3;"0.00"))>14;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>13;LEFT(RIGHT(TEXT(B3;"0.00");14);1);"");IF(LEN(TEXT(B3;"0.00"))>13;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>12;LEFT(RIGHT(TEXT(B3;"0.00");13);1);"");IF(LEN(TEXT(B3;"0.00"))>12;IF(AND(LEFT(RIGHT(TEXT(B3;"0.00");13);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");14);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");15);1)="0");"";"Milyar ");""));IF(LEN(TEXT(B3;"0.00"))>11;LEFT(RIGHT(TEXT(B3;"0.00");12);1);"");IF(LEN(TEXT(B3;"0.00"))>11;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>10;LEFT(RIGHT(TEXT(B3;"0.00");11);1);"");IF(LEN(TEXT(B3;"0.00"))>10;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>9;LEFT(RIGHT(TEXT(B3;"0.00");10);1);"");IF(LEN(TEXT(B3;"0.00"))>9;IF(AND(LEFT(RIGHT(TEXT(B3;"0.00");10);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");11);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");12);1)="0");"";"Juta ");""));IF(LEN(TEXT(B3;"0.00"))>8;LEFT(RIGHT(TEXT(B3;"0.00");9);1);"");IF(LEN(TEXT(B3;"0.00"))>8;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>7;LEFT(RIGHT(TEXT(B3;"0.00");8);1);"");IF(LEN(TEXT(B3;"0.00"))>7;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>6;LEFT(RIGHT(TEXT(B3;"0.00");7);1);"");IF(LEN(TEXT(B3;"0.00"))>6;IF(AND(LEFT(RIGHT(TEXT(B3;"0.00");7);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");8);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");9);1)="0");"";"Ribu ");""));IF(LEN(TEXT(B3;"0.00"))>5;LEFT(RIGHT(TEXT(B3;"0.00");6);1);"");IF(LEN(TEXT(B3;"0.00"))>5;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>4;LEFT(RIGHT(TEXT(B3;"0.00");5);1);"");IF(LEN(TEXT(B3;"0.00"))>4;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>3;LEFT(RIGHT(TEXT(B3;"0.00");4);1);""));"0";"Nol");"1";"Satu ");"2";"Dua ");"3";"Tiga ");"4";"Empat ");"5";"Lima ");"6";"Enam ");"7";"Tujuh ");"8";"Delapan ");"9";"Sembilan ");"Satu Puluh";"Sepuluh");"Sepuluh Satu";"Sebelas");"Sepuluh Dua";"Dua Belas");"Sepuluh Tiga";"Tiga Belas");"Sepuluh Empat";"Empat Belas");"Sepuluh Lima";"Lima Belas");"Sepuluh Enam";"Enam Belas");"Sepuluh Tujuh";"Tujuh Belas");"Sepuluh Delapan";"Delapan Belas");"Sepuluh Sembilan";"Sembilan Belas");"NolTriliun";"Triliun");"NolMilyar";"Milyar");"NolJuta";"Juta");" Nol Ribu";" Ribu");"NolRatus";"");"NolPuluh";"");"Satu Ribu";"Seribu ");"Satu Ratus";"Seratus ");" Nol";" ");" Seribu";" Satu Ribu")))&" Rupiah"
Rumus yang dibuat ini untuk angka/nominal antara 1 sampai dengan 1.000.000.000.000.000 (1Bilyun), sedangkan untuk desimal belum bisa digunakan.
1. ISBLANK, digunakan untuk memastikan cell yang dimaksud adalah kosong (tak bernilai).
2. SUBSTITUTE, digunakan untuk mengganti teks lama menjadi teks baru.
3. CONCATENATE, digunakan untuk menggabungkan beberapa teks.
4. TEXT, digunakan untuk merubah nilai numerik menjadi data string (teks).
5. LEFT, digunakan untuk mengambil karakter dari sebelah kiri.
6. RIGHT, digunakan untuk mengambil karakter dari sebelah kanan.
7. AND, digunakan untuk mengetahui dua atau lebih kondisi dalam keadaan benar.
8. LEN, digunakan untuk mengetahui jumlah panjang karakter.
9. IF, digunakan untuk menentukan satu atau lebih syarat atau kondisi.
Contoh: Pada kolom B5 nominalnya sebesar Rp 999.999.999.999.999,00 agar dapat terbaca dengan huruf ada beberapa langkah yang harus dilakukan:
1. IF(ISBLANK(B5),"",...
Jika kolom B5 kosong, maka hasilnya kosong, bila bernilai lanjut ke rumus selanjutnya.
Karena Kolom B5 bernilai, lanjut ke rumus berikutnya.
2. ...IF(LEN(TEXT(B5,"0.00"))>18,LEFT(RIGHT(TEXT(B5,"0.00"),19),1),""),
IF(LEN(TEXT(B5,"0.00"))>18,"Bilyun ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 18 karakter, maka ambil karakter sebanyak 19 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 18 karakter, tampilkan Bilyun, bila tidak kosongkan...
Karena pada kolom B5 total karakternya hanya 18, tidak lebih dari 18 maka hasilnya kosong.
Hasil IF ke-1 menghasilkan kosong
3. ...IF(LEN(TEXT(B5,"0.00"))>17,LEFT(RIGHT(TEXT(B5,"0.00"),18),1),""),
IF(LEN(TEXT(B5,"0.00"))>17,"Ratus","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 17 karakter, maka ambil karakter sebanyak 18 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 17 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 18 artinya lebih dari 17, maka diambil karakter ke-18 hasilnya 9.
Kolom B5 total karakternya ada 18 artinya lebih dari 17, maka tampilkan Ratus.
Hasil IF ke-2 menghasilkan 9Ratus.
4. ...IF(LEN(TEXT(B5,"0.00"))>16,LEFT(RIGHT(TEXT(B5,"0.00"),17),1),""),
IF(LEN(TEXT(B5,"0.00"))>16,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 16 karakter, maka ambil karakter sebanyak 17 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 16 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 17 artinya lebih dari 16, maka diambil karakter ke-17 hasilnya 9.
Kolom B5 total karakternya ada 17 artinya lebih dari 16, maka tampilkan Puluh.
Hasil IF ke-3 menghasilkan 9Puluh.
5. ...IF(LEN(TEXT(B5,"0.00"))>15,LEFT(RIGHT(TEXT(B5,"0.00"),16),1),""),
IF(LEN(TEXT(B5,"0.00"))>15,IF(AND(LEFT(RIGHT(TEXT(B5,"0.00"),16),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),17),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),18),1)="0"),"","Triliun "),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 15 karakter, maka ambil karakter sebanyak 16 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 15 karakter, dan jika karakter ke-16 = "0", karakter ke-17 = "0", karakter ke-18 = "0", hasilnya dikosongkan, kemudian tampilkan Triliun, bila tidak kosongkan...
Kolom B5 total karakternya ada 16 artinya lebih dari 15, maka diambil karakter ke-16 hasilnya 9.
Kolom B5 karakter ke-16, 17 dan 18 <> "0", dan
Kolom B5 total karakternya ada 16 artinya lebih dari 15, maka tampilkan Triliun.
Hasil IF ke-4 menghasilkan 9Triliun.
6. ...IF(LEN(TEXT(B5,"0.00"))>14,LEFT(RIGHT(TEXT(B5,"0.00"),15),1),""),
IF(LEN(TEXT(B5,"0.00"))>14,"Ratus ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 14 karakter, maka ambil karakter sebanyak 15 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 14 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 15 artinya lebih dari 14, maka diambil karakter ke-15 hasilnya 9.
Kolom B5 total karakternya ada 15 artinya lebih dari 14, maka tampilkan Ratus.
Hasil IF ke-5 menghasilkan 9Ratus.
7. ...IF(LEN(TEXT(B5,"0.00"))>13,LEFT(RIGHT(TEXT(B5,"0.00"),14),1),""),
IF(LEN(TEXT(B5,"0.00"))>13,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 13 karakter, maka ambil karakter sebanyak 14 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 13 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 14 artinya lebih dari 13, maka diambil karakter ke-14 hasilnya 9.
Kolom B5 total karakternya ada 14 artinya lebih dari 13, maka tampilkan Puluh.
Hasil IF ke-6 menghasilkan 9Puluh.
8. ...IF(LEN(TEXT(B5,"0.00"))>12,LEFT(RIGHT(TEXT(B5,"0.00"),13),1),""),
IF(LEN(TEXT(B5,"0.00"))>12,IF(AND(LEFT(RIGHT(TEXT(B5,"0.00"),13),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),14),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),15),1)="0"),"","Milyar "),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 12 karakter, maka ambil karakter sebanyak 13 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 12 karakter, dan jika karakter ke-13 = "0", karakter ke-14 = "0", karakter ke-15 = "0", hasilnya dikosongkan, kemudian tampilkan Milyar, bila tidak kosongkan...
Kolom B5 total karakternya ada 13 artinya lebih dari 12, maka diambil karakter ke-13 hasilnya 9.
Kolom B5 karakter ke-13, 14 dan 15 <> "0", dan
Kolom B5 total karakternya ada 13 artinya lebih dari 12, maka tampilkan Milyar.
Hasil IF ke-7 menghasilkan 9Milyar.
9. ...IF(LEN(TEXT(B5,"0.00"))>11,LEFT(RIGHT(TEXT(B5,"0.00"),12),1),""),
IF(LEN(TEXT(B5,"0.00"))>11,"Ratus ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 11 karakter, maka ambil karakter sebanyak 12 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 11 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 12 artinya lebih dari 11, maka diambil karakter ke-12 hasilnya 9.
Kolom B5 total karakternya ada 12 artinya lebih dari 11, maka tampilkan Ratus.
Hasil IF ke-8 menghasilkan 9Ratus.
10. ...IF(LEN(TEXT(B5,"0.00"))>10,LEFT(RIGHT(TEXT(B5,"0.00"),11),1),""),
IF(LEN(TEXT(B5,"0.00"))>10,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 10 karakter, maka ambil karakter sebanyak 11 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 10 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 11 artinya lebih dari 10, maka diambil karakter ke-11 hasilnya 9.
Kolom B5 total karakternya ada 11 artinya lebih dari 10, maka tampilkan Puluh.
Hasil IF ke-9 menghasilkan 9Puluh.
11. ...IF(LEN(TEXT(B5,"0.00"))>9,LEFT(RIGHT(TEXT(B5,"0.00"),10),1),""),
IF(LEN(TEXT(B5,"0.00"))>9,IF(AND(LEFT(RIGHT(TEXT(B5,"0.00"),10),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),11),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),12),1)="0"),"","Juta "),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 9 karakter, maka ambil karakter sebanyak 10 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 9 karakter, dan jika karakter ke-10 = "0", karakter ke-11 = "0", karakter ke-12 = "0", hasilnya dikosongkan, kemudian tampilkan Juta, bila tidak kosongkan...
Kolom B5 total karakternya ada 10 artinya lebih dari 9, maka diambil karakter ke-10 hasilnya 9.
Kolom B5 karakter ke-10, 11 dan 12 <> "0", dan
Kolom B5 total karakternya ada 10 artinya lebih dari 9, maka tampilkan Juta.
Hasil IF ke-10 menghasilkan 9Juta.
12. ...IF(LEN(TEXT(B5,"0.00"))>8,LEFT(RIGHT(TEXT(B5,"0.00"),9),1),""),
IF(LEN(TEXT(B5,"0.00"))>8,"Ratus ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 8 karakter, maka ambil karakter sebanyak 9 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 8 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 9 artinya lebih dari 8, maka diambil karakter ke-9 hasilnya 9.
Kolom B5 total karakternya ada 9 artinya lebih dari 8, maka tampilkan Ratus.
Hasil IF ke-11 menghasilkan 9Ratus.
13. ...IF(LEN(TEXT(B5,"0.00"))>7,LEFT(RIGHT(TEXT(B5,"0.00"),8),1),""),
IF(LEN(TEXT(B5,"0.00"))>7,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 7 karakter, maka ambil karakter sebanyak 8 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 7 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 8 artinya lebih dari 7, maka diambil karakter ke-8 hasilnya 9.
Kolom B5 total karakternya ada 8 artinya lebih dari 7, maka tampilkan Puluh.
Hasil IF ke-12 menghasilkan 9Puluh.
14. ...IF(LEN(TEXT(B5,"0.00"))>6,LEFT(RIGHT(TEXT(B5,"0.00"),7),1),""),
IF(LEN(TEXT(B5,"0.00"))>6,IF(AND(LEFT(RIGHT(TEXT(B5,"0.00"),7),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),8),1)="0",
LEFT(RIGHT(TEXT(B5,"0.00"),9),1)="0"),"","Ribu "),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 6 karakter, maka ambil karakter sebanyak 7 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 6 karakter, dan jika karakter ke-7 = "0", karakter ke-8 = "0", karakter ke-9 = "0", hasilnya dikosongkan, kemudian tampilkan Ribu, bila tidak kosongkan...
Kolom B5 total karakternya ada 7 artinya lebih dari 6, maka diambil karakter ke-7 hasilnya 9.
Kolom B5 karakter ke-7, 8 dan 9 <> "0", dan
Kolom B5 total karakternya ada 7 artinya lebih dari 6, maka tampilkan Ribu.
Hasil IF ke-13 menghasilkan 9Ribu.
15. ...IF(LEN(TEXT(B5,"0.00"))>5,LEFT(RIGHT(TEXT(B5,"0.00"),6),1),""),
IF(LEN(TEXT(B5,"0.00"))>5,"Ratus ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 5 karakter, maka ambil karakter sebanyak 6 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 5 karakter, tampilkan Ratus, bila tidak kosongkan...
Kolom B5 total karakternya ada 6 artinya lebih dari 5, maka diambil karakter ke-6 hasilnya 9.
Kolom B5 total karakternya ada 6 artinya lebih dari 5, maka tampilkan Ratus.
Hasil IF ke-14 menghasilkan 9Ratus.
16. ...IF(LEN(TEXT(B5,"0.00"))>4,LEFT(RIGHT(TEXT(B5,"0.00"),5),1),""),
IF(LEN(TEXT(B5,"0.00"))>4,"Puluh ","")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 4 karakter, maka ambil karakter sebanyak 5 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan, Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 4 karakter, tampilkan Puluh, bila tidak kosongkan...
Kolom B5 total karakternya ada 5 artinya lebih dari 4, maka diambil karakter ke-5 hasilnya 9.
Kolom B5 total karakternya ada 5 artinya lebih dari 4, maka tampilkan Puluh.
Hasil IF ke-15 menghasilkan 9Puluh.
17. ...IF(LEN(TEXT(B5,"0.00"))>3,LEFT(RIGHT(TEXT(B5,"0.00"),4),1),"")),...
Jika total karakter pada kolom B5 setelah dirubah dari numerik menjadi teks dengan format teks "0.00" lebih dari 3 karakter, maka ambil karakter sebanyak 4 dari kanan teks B5 lalu ambil 1 karakternya dari kiri, bila tidak kosongkan...
Kolom B5 total karakternya ada 4 artinya lebih dari 3, maka diambil karakter ke-4 hasilnya 9.
Hasil IF ke-16 menghasilkan 9.
Dari rumus diperoleh hasil:
IF ke-1 menghasilkan kosong (Diabaikan)
IF ke-2 menghasilkan 9Ratus.
IF ke-3 menghasilkan 9Puluh.
IF ke-4 menghasilkan 9Triliun.
IF ke-5 menghasilkan 9Ratus.
IF ke-6 menghasilkan 9Puluh.
IF ke-7 menghasilkan 9Milyar.
IF ke-8 menghasilkan 9Ratus.
IF ke-9 menghasilkan 9Puluh.
IF ke-10 menghasilkan 9Juta.
IF ke-11 menghasilkan 9Ratus.
IF ke-12 menghasilkan 9Puluh.
IF ke-13 menghasilkan 9Ribu.
IF ke-14 menghasilkan 9Ratus.
IF ke-15 menghasilkan 9Puluh.
IF ke-16 menghasilkan 9.
Apabila hasil rumus dari IF ke-1 s/d IF ke-17 digabungkan dengan fungsi CONCATENATE, karena hasil yang akan digabungkan ada 16 teks, maka penulisan fungsi CONCATENATE juga sebanyak 16, penulisannya CONCATENATE(CONCATENATE(.... sebanyak 16x.
Dari hasil penggabungan akan menghasilkan kalimat:
" 9Ratus9Puluh9Triliun9Ratus9Puluh9Milyar9Ratus9Puluh9Juta9Ratus9Puluh9Ribu9Ratus
9Puluh9 ".
Kalimat tersebut masih belum sempurna, masih dalam bentuk asli hasil pembacaan dari kolaborasi rumus. Masih berwujud angka, dari kata ke kata juga masih rapat belum terdapat spasi sebagai pemisah antar kata.
Untuk menyempurnakan hasil pembacaan rumus tersebut, perlu dilakukan penggantian terhadap teks yang dihasilkan oleh rumus. Oleh karena itu, diperlukan fungsi SUBSTITUTE untuk mengganti teks yang lebih baik dan rapi apabila dibaca.
Teks yang perlu dilakukan perubahan atau penggantian ada 30 teks, yaitu:
"0";"Nol");"1";"Satu ");"2";"Dua ");"3";"Tiga ");"4";"Empat ");"5";"Lima ");"6";"Enam ");"7";"Tujuh ");"8";"Delapan ");"9";"Sembilan ");"Satu Puluh";"Sepuluh");"Sepuluh Satu";"Sebelas");"Sepuluh Dua";"Dua Belas");"Sepuluh Tiga";"Tiga Belas");"Sepuluh Empat";"Empat Belas");"Sepuluh Lima";"Lima Belas");"Sepuluh Enam";"Enam Belas");"Sepuluh Tujuh";"Tujuh Belas");"Sepuluh Delapan";"Delapan Belas");"Sepuluh Sembilan";"Sembilan Belas");"NolTriliun";"Triliun");"NolMilyar";"Milyar");"NolJuta";"Juta");" Nol Ribu";" Ribu");"NolRatus";"");"NolPuluh";"");"Satu Ribu";"Seribu ");"Satu Ratus";"Seratus ");" Nol";" ");" Seribu";" Satu Ribu")))&" Rupiah"
Sama halnya seperti fungsi CONCATENATE, penulisan fungsi SUBSTITUTE juga sebanyak teks yang akan dilakukan penggantiannya, karena terdapat 29 teks yang dirubah atau diganti, maka penulisannya SUBSTITUTE(SUBSTITUTE(... sebanyak 29x.
Sehingga hasil dari perubahan atau penggantian teks hasil pembacaan rumus dengan teks yang baru seperti yang dibuatkan aturannya (parameternya), contoh teks "9" diganti jadi "Sembilan ", dan seterusnya, menghasilkan kalimat yang sempurna:
"Sembilan Ratus Sembilan Puluh Sembilan Triliun Sembilan Ratus Sembilan Puluh Sembilan Milyar Sembilan Ratus Sembilan Puluh Sembilan Juta Sembilan Ratus Sembilan Puluh Sembilan Ribu Sembilan Ratus Sembilan Puluh Sembilan Rupiah".
Rumus lengkapnya:
=TRIM(IF(ISBLANK(B3);"Kolom B3 Kosong";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(IF(LEN(TEXT(B3;"0.00"))>18;LEFT(RIGHT(TEXT(B3;"0.00");19);1);"");IF(LEN(TEXT(B3;"0.00"))>18;"Bilyun ";""));IF(LEN(TEXT(B3;"0.00"))>17;LEFT(RIGHT(TEXT(B3;"0.00");18);1);"");IF(LEN(TEXT(B3;"0.00"))>17;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>16;LEFT(RIGHT(TEXT(B3;"0.00");17);1);"");IF(LEN(TEXT(B3;"0.00"))>16;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>15;LEFT(RIGHT(TEXT(B3;"0.00");16);1);"");IF(LEN(TEXT(B3;"0.00"))>15;IF(AND(LEFT(RIGHT(TEXT(B3;"0.00");16);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");17);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");18);1)="0");"";"Triliun ");""));IF(LEN(TEXT(B3;"0.00"))>14;LEFT(RIGHT(TEXT(B3;"0.00");15);1);"");IF(LEN(TEXT(B3;"0.00"))>14;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>13;LEFT(RIGHT(TEXT(B3;"0.00");14);1);"");IF(LEN(TEXT(B3;"0.00"))>13;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>12;LEFT(RIGHT(TEXT(B3;"0.00");13);1);"");IF(LEN(TEXT(B3;"0.00"))>12;IF(AND(LEFT(RIGHT(TEXT(B3;"0.00");13);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");14);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");15);1)="0");"";"Milyar ");""));IF(LEN(TEXT(B3;"0.00"))>11;LEFT(RIGHT(TEXT(B3;"0.00");12);1);"");IF(LEN(TEXT(B3;"0.00"))>11;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>10;LEFT(RIGHT(TEXT(B3;"0.00");11);1);"");IF(LEN(TEXT(B3;"0.00"))>10;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>9;LEFT(RIGHT(TEXT(B3;"0.00");10);1);"");IF(LEN(TEXT(B3;"0.00"))>9;IF(AND(LEFT(RIGHT(TEXT(B3;"0.00");10);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");11);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");12);1)="0");"";"Juta ");""));IF(LEN(TEXT(B3;"0.00"))>8;LEFT(RIGHT(TEXT(B3;"0.00");9);1);"");IF(LEN(TEXT(B3;"0.00"))>8;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>7;LEFT(RIGHT(TEXT(B3;"0.00");8);1);"");IF(LEN(TEXT(B3;"0.00"))>7;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>6;LEFT(RIGHT(TEXT(B3;"0.00");7);1);"");IF(LEN(TEXT(B3;"0.00"))>6;IF(AND(LEFT(RIGHT(TEXT(B3;"0.00");7);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");8);1)="0";LEFT(RIGHT(TEXT(B3;"0.00");9);1)="0");"";"Ribu ");""));IF(LEN(TEXT(B3;"0.00"))>5;LEFT(RIGHT(TEXT(B3;"0.00");6);1);"");IF(LEN(TEXT(B3;"0.00"))>5;"Ratus ";""));IF(LEN(TEXT(B3;"0.00"))>4;LEFT(RIGHT(TEXT(B3;"0.00");5);1);"");IF(LEN(TEXT(B3;"0.00"))>4;"Puluh ";""));IF(LEN(TEXT(B3;"0.00"))>3;LEFT(RIGHT(TEXT(B3;"0.00");4);1);""));"0";"Nol");"1";"Satu ");"2";"Dua ");"3";"Tiga ");"4";"Empat ");"5";"Lima ");"6";"Enam ");"7";"Tujuh ");"8";"Delapan ");"9";"Sembilan ");"Satu Puluh";"Sepuluh");"Sepuluh Satu";"Sebelas");"Sepuluh Dua";"Dua Belas");"Sepuluh Tiga";"Tiga Belas");"Sepuluh Empat";"Empat Belas");"Sepuluh Lima";"Lima Belas");"Sepuluh Enam";"Enam Belas");"Sepuluh Tujuh";"Tujuh Belas");"Sepuluh Delapan";"Delapan Belas");"Sepuluh Sembilan";"Sembilan Belas");"NolTriliun";"Triliun");"NolMilyar";"Milyar");"NolJuta";"Juta");" Nol Ribu";" Ribu");"NolRatus";"");"NolPuluh";"");"Satu Ribu";"Seribu ");"Satu Ratus";"Seratus ");" Nol";" ");" Seribu";" Satu Ribu")))&" Rupiah"
..:: Semoga Bermanfaat ::..
Sampeyan memang super mas, terima kasih buanyak banget
ReplyDeletetanpa macro, tanpa tabel bantuan dst
Alhamdulillah,
DeletePernah melihat rumus serupa sebelumnya dan saya coba alhamdulillah berhasil meskipun masih ada kekurangan.
Maaf lama tidak keurus...
thx gan rumusnya
ReplyDeleteSama2 Mas Satriansyah
Deleteada bug nya bang di nilai seribu misal 1.101.000 nilasinya "Satu Juta Seratus Seribu Rupiah" harusnya "Satu Juta Seratus Satu Ribu Rupiah" .. thx
ReplyDeleteSetelah diperbaiki:
DeleteSudah tidak ada lagi bugnya.
DeleteKurang Satu Substitute, pada rumus sebelumnya diakhir formula:
...."Nol",""))&"Rupiah"
Seharusnya masih ada satu lagi Substitute setelah "Nol","")
ditambahkan dengan ," Seribu";" Satu Ribu")) jangan lupa pada barisan SUBSTITUTE juga ditambahkan SUBSTITUTE( satu lagi.
Terimakasih,
Semoga Bermanfaat
Sangat suka sekali....makasih banyak...banyak sekali... :)
ReplyDeleteTerimakasih Kak Sari Kartika
DeleteSudah mampir di GUBUGEXCEL
saya coba rumus lengkapnya kok error bang?
ReplyDeleteKak Anonymous, Errornya seperti apa yah?
DeleteTanda Baca pada rumus ini mengghunakan tanda koma (,)
Disamakan persepsinya dahulu, apabila Settingan diExcelnya menggunakan tanda baca titik koma (;) maka copy pastekan formula terlebih dahulu ke Notepad (lakukan replace all untuk tanda baca).
Jika error terjadi karena perbedaan tanda baca yang digunakan.
Apabila masih terdapat error, bisa di ScreenShoot errornya agar bisa dianalisa dan ditemukan solusinya.
Terimakasih.
gokilll
ReplyDeletejempol dah
Dua Jempol juga buat Kak Rio Devinda..
DeleteTerimakasih.
Mantap... bagaimna klo format tgl: 01/01/2015 mau diubah menjadi satu bulan januari tahun dua ribu lima belas.
ReplyDeleteTrims
Kang Asep, sebelumnya terimakasih dah berkunjung ke Gubu Excel..
DeleteMangga kang Asep dicoba dengan rumus Gelonya ini, Siapkan data tanggal pada Kolom B3 dulu yak.. kemudian pastekan Rumus Gelo ini di kolom lain:
=TRIM(IF(ISBLANK(B3);"Kolom B3 Kosong";"Hari "&TEXT(B3;"DDDD")&","&" Tanggal "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(DAY(B3))&" Bulan "&TEXT(B3;"MMMM")&" Tahun "&IF(LEN(TEXT(YEAR(B3);"0.00"))>6;LEFT(RIGHT(TEXT(YEAR(B3);"0.00");7);1);"");IF(LEN(TEXT(YEAR(B3);"0.00"))>6;IF(AND(LEFT(RIGHT(TEXT(YEAR(B3);"0.00");7);1)="0";LEFT(RIGHT(TEXT(YEAR(B3);"0.00");8);1)="0";LEFT(RIGHT(TEXT(YEAR(B3);"0.00");9);1)="0");"";"Ribu ");""));IF(LEN(TEXT(YEAR(B3);"0.00"))>5;LEFT(RIGHT(TEXT(YEAR(B3);"0.00");6);1);"");IF(LEN(TEXT(YEAR(B3);"0.00"))>5;"Ratus ";""));IF(LEN(TEXT(YEAR(B3);"0.00"))>4;LEFT(RIGHT(TEXT(YEAR(B3);"0.00");5);1);"");IF(LEN(TEXT(YEAR(B3);"0.00"))>4;"Puluh ";""));IF(LEN(TEXT(YEAR(B3);"0.00"))>3;LEFT(RIGHT(TEXT(YEAR(B3);"0.00");4);1);""));"0";"Nol");"1";"Satu ");"2";"Dua ");"3";"Tiga ");"4";"Empat ");"5";"Lima ");"6";"Enam ");"7";"Tujuh ");"8";"Delapan ");"9";"Sembilan ");"Satu Puluh";"Sepuluh");"Sepuluh Satu";"Sebelas");"Sepuluh Dua";"Dua Belas");"Sepuluh Tiga";"Tiga Belas");"Sepuluh Empat";"Empat Belas");"Sepuluh Lima";"Lima Belas");"Sepuluh Enam";"Enam Belas");"Sepuluh Tujuh";"Tujuh Belas");"Sepuluh Delapan";"Delapan Belas");"Sepuluh Sembilan";"Sembilan Belas");" Nol Ribu";" Ribu");"NolRatus";"");"NolPuluh";"");"Satu Ribu";"Seribu ");"Satu Ratus";"Seratus ");" Nol";" ");" Seribu";" Satu Ribu")))
ini apa masalah nya gan...
ReplyDelete1.500
hasil nya Sepuluh . Rupiah
iya sama... ada rumus yang korup kayaknya...
Deletesangat membantu sekali...
ReplyDeleteterima kasih banyak mas...
btw kalo di save as dengan excel 1997-2003 rumusnya gak kebaca
SANGAT MEMBANTU..DAN TERIMA KASIH SEBELUMNYA..
ReplyDeleteMAU TANYA BAGAIMANA MISALKAN ADA ANGKA DESIMAL?
CONTOH : 123.456,08 ATAU 123.456,89 RUMUSNYA BAGAIMANA?
mohon bantuannya untuk penulisan 01/01/2020 menjadi hari Senin tanggal satu bulan januari tahun dua ribu dua puluh.
ReplyDeleteSaya sdh mencoba cara diatas tapi belum tepat.
terimakasih
hatur nuhun banyak kang, mugi barokah ilmu yang telah di share...
ReplyDeletebagi yang gagal (katanya ada bug), silakan tanda . pada angka desimal (0.00) diganti dengan tanda koma (0,00).
jazakallah khoir