Makasih Mas, Ternyata tadi keburu2.
Terlampir revisinya di bagian ribuan menjadi
&REPT(" ribu ",(A7>=2000)*(--MID(TEXT(A7,frm),4,3)>0))
Ternyata lebih mudah yang versi inggrinya.
Maturnuwun,
-zainul-
From: [email protected] [mailto:[email protected]]
On Behalf Of summonery
Sent: Sunday, November 13, 2011 9:10 PM
To: [email protected]
Subject: [belajar-excel] Re: Mega Formula untuk Fungsi Terbilang
coba lagi,
1,200,000
2,100,000
3,100,100
--- In [email protected]
<mailto:belajar-excel%40yahoogroups.com> , "zainul_ulum[at]yahoo[dot]com"
<zainul_ulum@...> wrote:
>
> Terimakasih banyak mas Anton
> Terlampir revisinya
>
> Thanks
> -zainul-
>
> From: [email protected]
<mailto:belajar-excel%40yahoogroups.com>
[mailto:[email protected]
<mailto:belajar-excel%40yahoogroups.com> ]
> On Behalf Of summonery
> Sent: Sunday, November 13, 2011 7:29 PM
> To: [email protected] <mailto:belajar-excel%40yahoogroups.com>
> 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]
<mailto:belajar-excel%40yahoogroups.com> , "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-
> >
>
<<image001.jpg>>
<<image002.jpg>>
Terbilangv7.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
