Membuat Kode Formula Terbilang Pada Microsoft Excel

Tujuan dari Formula ini adalah menjadikan Otomatis Huruf pada Nilai Raport atau Terbilang otomatis pada Terbilang Kwuitansi dan Laporan Keuangan pada microsoft Exel. Dengan demikian kita dapat memangkas waktu kerja karena tidak harus mengetik terbilang-terbilang tersebut.

FORMULA EXCEL BAGIAN 1

=IF(A1=0;"nol";IF(A1<0;"minus ";"")&

SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT

UTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT

UTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT

UTE(SUBSTITUTE( IF(--

MID(TEXT(ABS(A1);"000000000000000");1;3)=0;"";MID(TEXT(ABS(A1);"000000000000000");1;1)&"

ratus "&MID(TEXT(ABS(A1);"000000000000000");2;1)&" puluh

"&MID(TEXT(ABS(A1);"000000000000000");3;1)&" trilyun ")& IF(--

MID(TEXT(ABS(A1);"000000000000000");4;3)=0;"";MID(TEXT(ABS(A1);"000000000000000");4;1)&"

ratus "&MID(TEXT(ABS(A1);"000000000000000");5;1)&" puluh

"&MID(TEXT(ABS(A1);"000000000000000");6;1)&" milyar ")& IF(--

MID(TEXT(ABS(A1);"000000000000000");7;3)=0;"";MID(TEXT(ABS(A1);"000000000000000");7;1)&"

ratus "&MID(TEXT(ABS(A1);"000000000000000");8;1)&" puluh

"&MID(TEXT(ABS(A1);"000000000000000");9;1)&" juta ")& IF(--

MID(TEXT(ABS(A1);"000000000000000");10;3)=0;"";IF(--

MID(TEXT(ABS(A1);"000000000000000");10;3)=1;"*";MID(TEXT(ABS(A1);"000000000000000");10;1)&"

ratus "&MID(TEXT(ABS(A1);"000000000000000");11;1)&" puluh

")&MID(TEXT(ABS(A1);"000000000000000");12;1)&" ribu ")& IF(--

MID(TEXT(ABS(A1);"000000000000000");13;3)=0;"";MID(TEXT(ABS(A1);"000000000000000");13;1)&"

ratus "&MID(TEXT(ABS(A1);"000000000000000");14;1)&" puluh

"&MID(TEXT(ABS(A1);"000000000000000");15;1));1;"satu");2;"dua");3;"tiga");4;"empat");5;"lima");6;"e

nam");7;"tujuh");8;"delapan");9;"sembilan");"0 ratus";"");"0 puluh";"");"satu puluh 0";"sepuluh");"satu

puluh satu";"sebelas");"satu puluh dua";"duabelas");"satu puluh tiga";"tigabelas");"satu puluh

empat";"empatbelas");"satu puluh lima";"limabelas");"satu puluh enam";"enambelas");"satu puluh

tujuh";"tujuhbelas");"satu puluh delapan";"delapanbelas");"satu puluh

sembilan";"sembilanbelas");"satu ratus";"seratus");"*satu ribu";"seribu");0;""));" ";" "))

 

FORMULA EXCEL BAGIAN 2

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUB

STITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1;"#,00"); 0

;"Nol ");1;"Satu ");2;"Dua ");3;"Tiga ");4;"Empat ");5;"Lima

");6;"Enam ");7;"Tujuh ");8;"Delapan ");9;"Sembilan ");",";"Koma