Kumpulan Rumus Kocak Terbilang buat EXCEL

 RUMUS KOCAK, siapa tau ada yang berminat
(menghasilkan huruf angka yang ditulis)
cocok untuk penulisan ANGKA pada Rapor
=IF(J15="";"";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(IF(LEN(TEXT(J15;"0,00"))>9;LEFT(RIGHT(TEXT(J15;"0,00");10);1);"");IF(LEN(TEXT(J15;"0,00"))>9;"Juta ";""));IF(LEN(TEXT(J15;"0,00"))>8;LEFT(RIGHT(TEXT(J15;"0,00");9);1);"");IF(LEN(TEXT(J15;"0,00"))>8;"Ratus ";"");IF(LEN(TEXT(J15;"0,00"))>7;LEFT(RIGHT(TEXT(J15;"0,00");8);1);"");IF(LEN(TEXT(J15;"0,00"))>7;"Puluh ";"")));IF(LEN(TEXT(J15;"0,00"))>6;LEFT(RIGHT(TEXT(J15;"0,00");7);1);"");IF(LEN(TEXT(J15;"0,00"))<7;"";IF(AND(LEFT(RIGHT(TEXT(J15;"0,00");7);1)="0";LEFT(RIGHT(TEXT(J15;"0,00");8);1)="0";LEFT(RIGHT(TEXT(J15;"0,00");9);1)="0")=TRUE;"";"Ribu "))));IF(LEN(TEXT(J15;"0,00"))>5;LEFT(RIGHT(TEXT(J15;"0,00");6);1);"");IF(LEN(TEXT(J15;"0,00"))>5;"Ratus ";""));IF(LEN(TEXT(J15;"0,00"))>4;LEFT(RIGHT(TEXT(J15;"0,00");5);1);"");IF(LEN(TEXT(J15;"0,00"))>4;"Puluh ";"");CONCATENATE(IF(LEFT(RIGHT(TEXT(J15;"0,00");4);1)="0";"";LEFT(RIGHT(TEXT(J15;"0,00");4);1));CONCATENATE("koma ";MID(RIGHT(TEXT(J15;"0,00");2);1;1);" ";RIGHT(RIGHT(TEXT(J15;"0,00");2);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 ";"Duabelas ");"Sepuluh Tiga ";"Tigabelas ");"Sepuluh Empat ";"Empatbelas ");"Sepuluh Lima ";"Limabelas ");"Sepuluh Enam ";"Enambelas");"Sepuluh Tujuh ";"Tujuhbelas ");"Sepuluh Delapan ";"Delapanbelas ");"Sepuluh Sembilan ";"Sembilanbelas ");"Nol Puluh ";"");"Satu Ribu ";"Seribu ");"Nol Ratus ";"");"Nol Ribu ";"Ribu ");"Satu Ratus ";"Seratus ");"Juta Nol";"Juta "))



RUMUS KOCAK, siapa tau ada yang berminat
(menghasilkan huruf angka yang ditulis) / Rupiah)
=CONCATENATE(SUBSTITUTE(IF(ROUND(J15;0)="";"";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(IF(LEN(TEXT(ROUND(J15;0);"0,00"))>9;LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");10);1);"");IF(LEN(TEXT(ROUND(J15;0);"0,00"))>9;"Juta ";""));IF(LEN(TEXT(ROUND(J15;0);"0,00"))>8;LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");9);1);"");IF(LEN(TEXT(ROUND(J15;0);"0,00"))>8;"Ratus ";"");IF(LEN(TEXT(ROUND(J15;0);"0,00"))>7;LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");8);1);"");IF(LEN(TEXT(ROUND(J15;0);"0,00"))>7;"Puluh ";"")));IF(LEN(TEXT(ROUND(J15;0);"0,00"))>6;LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");7);1);"");IF(LEN(TEXT(ROUND(J15;0);"0,00"))<7;"";IF(AND(LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");7);1)="0";LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");8);1)="0";LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");9);1)="0")=TRUE;"";"Ribu "))));IF(LEN(TEXT(ROUND(J15;0);"0,00"))>5;LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");6);1);"");IF(LEN(TEXT(ROUND(J15;0);"0,00"))>5;"Ratus ";""));IF(LEN(TEXT(ROUND(J15;0);"0,00"))>4;LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");5);1);"");IF(LEN(TEXT(ROUND(J15;0);"0,00"))>4;"Puluh ";"");CONCATENATE(IF(LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");4);1)="0";"";LEFT(RIGHT(TEXT(ROUND(J15;0);"0,00");4);1));CONCATENATE(", ";MID(RIGHT(TEXT(ROUND(J15;0);"0,00");2);1;1);" ";RIGHT(RIGHT(TEXT(ROUND(J15;0);"0,00");2);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 ";"Duabelas ");"Sepuluh Tiga ";"Tigabelas ");"Sepuluh Empat ";"Empatbelas ");"Sepuluh Lima ";"Limabelas ");"Sepuluh Enam ";"Enambelas");"Sepuluh Tujuh ";"Tujuhbelas ");"Sepuluh Delapan ";"Delapanbelas ");"Sepuluh Sembilan ";"Sembilanbelas ");"Nol Puluh ";"");"Satu Ribu ";"Seribu ");"Nol Ratus ";"");"Nol Ribu ";"Ribu ");"Satu Ratus ";"Seratus ");"Juta Nol";"Juta "));", Nol Nol ";"");" Rupiah")


RUMUS KOCAK fersi bahasa ingris, siapa tau ada yang berminat
(menghasilkan huruf angka yang ditulis) / Rupiah)
=IF(LEN(INT(A4))>LEN(_frm);"‪#‎Maximal‬ 12 digit#";
INDEX(ListNumber;MID(TEXT(A4;_frm);1;1)+1)
&REPT(" hundred ";0+MID(TEXT(A4;_frm);1;3)>99)
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);2;2)<20)*MID(TEXT(A4;_frm);2;2)+1)
&INDEX(ListNumber_;(0+MID(TEXT(A4;_frm);2;2)>=20)*(0+MID(TEXT(A4;_frm);2;2)<=59)*(MID(TEXT(A4;_frm);2;1)))
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);2;2)>59)*MID(TEXT(A4;_frm);2;1)+1)
&REPT("ty";0+MID(TEXT(A4;_frm);2;2)>59)
&REPT("-";0+MID(TEXT(A4;_frm);3;1)>0)
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);2;2)>=20)*MID(TEXT(A4;_frm);3;1)+1)
&REPT(" billion ";LEN(A4)>9)

&INDEX(ListNumber;MID(TEXT(A4;_frm);4;1)+1)
&REPT(" hundred ";0+MID(TEXT(A4;_frm);4;3)>99)
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);5;2)<20)*MID(TEXT(A4;_frm);5;2)+1)
&INDEX(ListNumber_;(0+MID(TEXT(A4;_frm);5;2)>=20)*(0+MID(TEXT(A4;_frm);5;2)<=59)*(MID(TEXT(A4;_frm);5;1)))
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);5;2)>59)*MID(TEXT(A4;_frm);5;1)+1)
&REPT("ty";0+MID(TEXT(A4;_frm);5;2)>59)
&REPT("-";0+MID(TEXT(A4;_frm);6;1)>0)
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);5;2)>=20)*MID(TEXT(A4;_frm);6;1)+1)
&REPT(" million ";(0+MID(TEXT(A4;_frm);4;3)>0))
&INDEX(ListNumber;MID(TEXT(A4;_frm);7;1)+1)
&REPT(" hundred ";0+MID(TEXT(A4;_frm);7;3)>99)
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);8;2)<20)*MID(TEXT(A4;_frm);8;2)+1)
&INDEX(ListNumber_;(0+MID(TEXT(A4;_frm);8;2)>=20)*(0+MID(TEXT(A4;_frm);8;2)<=59)*(MID(TEXT(A4;_frm);8;1)))
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);8;2)>59)*MID(TEXT(A4;_frm);8;1)+1)
&REPT("ty";0+MID(TEXT(A4;_frm);8;2)>59)
&REPT("-";0+MID(TEXT(A4;_frm);9;1)>0)
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);8;2)>=20)*MID(TEXT(A4;_frm);9;1)+1)
&REPT(" thousand ";0+MID(TEXT(A4;_frm);7;3)>0)
&INDEX(ListNumber;MID(TEXT(A4;_frm);10;1)+1)
&REPT(" hundred ";(0+MID(TEXT(A4;_frm);10;3))>99)
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);11;2)<20)*MID(TEXT(A4;_frm);11;2)+1)
&INDEX(ListNumber_;(0+MID(TEXT(A4;_frm);11;2)>=20)*(0+MID(TEXT(A4;_frm);11;2)<=59)*(MID(TEXT(A4;_frm);11;1)))
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);11;2)>59)*MID(TEXT(A4;_frm);11;1)+1)
&REPT("ty";0+MID(TEXT(A4;_frm);11;2)>59)
&REPT("-";0+MID(TEXT(A4;_frm);12;1)>0)
&INDEX(ListNumber;(0+MID(TEXT(A4;_frm);11;2)>=20)*MID(TEXT(A4;_frm);12;1)+1)
)

 
Yang suka main2 di VBA. silakan kopi paste codenya
rumus terbilang
Function TerbilangNilai(Bil As Double) As String
Dim Utuh As Long, Pecah As Long, StrUtuh As String, StrPecah As String
Utuh = Int(Bil): Pecah = Round((Bil - Utuh) * 100, 0)
If Utuh = 0 Then StrUtuh = "Nol " Else StrUtuh = Translator(Utuh)
StrPecah = Translator2(Pecah)
TerbilangNilai = StrUtuh & " Koma " & StrPecah
End Function

Private Function Translator2(Pecahan) As String
Const TxAngka As String = "Nol,Satu,Dua,Tiga,Empat,Lima,Enam,Tujuh,Delapan,Sembilan"
Dim ArUcap, sPecah As String
ArUcap = Split(TxAngka, ",")
sPecah = Format(Pecahan, "00")
Translator2 = ArUcap(CInt(Mid(sPecah, 1, 1))) & " " & ArUcap(CInt(Mid(sPecah, 2, 1)))
End Function