coba lagi, 1,200,000 2,100,000 3,100,100
--- In [email protected], "zainul_ulum[at]yahoo[dot]com" <zainul_ulum@...> wrote: > > Terimakasih banyak mas Anton > Terlampir revisinya > > Thanks > -zainul- > > From: [email protected] [mailto:[email protected]] > On Behalf Of summonery > Sent: Sunday, November 13, 2011 7:29 PM > To: [email protected] > Subject: [belajar-excel] Re: Mega Formula untuk Fungsi Terbilang > > > silakan dicoba untuk: > 2,000 > 1,000,001 > 11,000,001 > 1,000,000,001 > 1,000,100,101 > 100,000,000,000 > 111,000,000,000 > 111,000,000,001 > > > >semoga bermanfaat > > > > > *topik tenggelam dalam topik > > --- In [email protected], "zainul_ulum[at]yahoo[dot]com" > <zainul_ulum@> wrote: > > > > 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- > > >
