Be-Exceler, Mohon bantuan untuk menguji fungsi mega formula yang aku buat untuk fungsi terbilang untuk angka bulat dari 1 sampai 999999999
Fungsi ini menggunakan bantuan dua buah [static named ranges], yaitu
1. frm
refers to ="000000000"
2. ListHuruf
refers to
={"","satu","dua","tiga","empat","lima","enam","tujuh","delapan","sembilan",
"sepuluh","sebelas","dua belas","tiga belas","empat belas","lima
belas","enam belas","tujuh belas","delapan belas","sembilan belas"}
Mega Formulanya dengan asumsi angka yang akan disebut di cell A2
=SUBSTITUTE(INDEX(ListHuruf,MID(TEXT(A2,frm),1,1)+1)
&REPT(" ratus ",-MID(TEXT(A2,frm),1,3)>99)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)<20)*MID(TEXT(A2,frm),2,2)+1)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID(TEXT(A2,frm),2,1)+1)
&REPT(" puluh
",-MID(TEXT(A2,frm),2,2)>19)&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID
(TEXT(A2,frm),3,1)+1)
&REPT(" juta ",LEN(A2)>6)
&INDEX(ListHuruf,MID(TEXT(A2,frm),4,1)+1)
&REPT(" ratus ",-MID(TEXT(A2,frm),4,3)>99)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)<20)*MID(TEXT(A2,frm),5,2)*(A2>2000)
+1)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),5,1)*(A2>2000)
+1)
&REPT(" puluh ",-MID(TEXT(A2,frm),5,2)>19)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),6,1)+1)
&REPT("seribu ",(A2<2000)*(LEN(A2)=4))
&REPT(" ribu ",(A2>2000))
&INDEX(ListHuruf,MID(TEXT(A2,frm),7,1)+1)
&REPT(" ratus ",-MID(TEXT(A2,frm),7,3)>99)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)<20)*MID(TEXT(A2,frm),8,2)+1)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),8,1)+1)
&REPT(" puluh ",-MID(TEXT(A2,frm),8,2)>19)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),9,1)+1),"satu
ratus","seratus")
Contoh file terlampir
Terimakasih
-zainul-
Terbilangv6.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
