Formula Terbilang bagian 2 pada Gnumeric

• Buat terlebih dahulu pada kolom B dan C, sebuah tabel untuk proses LOOKUP angka-nya, sesuai dengan isi pada tabel di bawah ini:

Baris	Kolom B	Kolom C
1	0	-
2	1	satu
3	2	dua
4	3	tiga
5	4	empat
6	5	lima
7	6	enam
8	7	tujuh
9	8	delapan
10	9	sembilan

• Lalu isi kolom A dengan semua formula yang ada pada tabel di bawah ini

Sel	Isi	Contoh	Keterangan
A1	Sembarang angka	6,876,527,518,768.56	Nilai Inputan
A2	=text(A1,rept("0",15)&".00")	006876527518768.56	Format Teks "000,000,000,000,000,000.00"
A3	=left(A2,15)	006876527518768	Bagian 15 digit di depan koma
A4	=right(left(A3,len(A3)-12),3)	006	3 digit triliun
A5	=left(A4,1)	0
A6	=mid(A4,2,1)	0
A7	=right(A4,1)	6
A8	=right(left(A3,len(A3)-9),3)	876	3 digit milyar
A9	=left(A8,1)	8
A10	=mid(A8,2,1)	7
A11	=right(A8,1)	6
A12	=right(left(A3,len(A3)-6),3)	527	3 digit juta
A13	=left(A12,1)	5
A14	=mid(A12,2,1)	2
A15	=right(A12,1)	7
A16	=left(right(A3,6),3)	518	3 digit ribu
A17	=left(A16,1)	5
A18	=mid(A16,2,1)	1
A19	=right(A16,1)	8
A20	=right(A3,3)	768	3 digit satuan
A21	=left(A20,1)	7
A22	=mid(A20,2,1)	6
A23	=right(A20,1)	8
A24	=right(A2,2)	56	Bagian 2 digit di belakang koma
A25	=left(A24,1)	5
A26	=right(A24,1)	6
A27	=substitute(if(-(-A5)>0,lookup(-(-A5),$B$1:$B$10,$C$1:$C$10)&" ratus ",""),"satu ratus","seratus")		Konversi Trilyun1
A28	=substitute(if(-(-A6)>1,lookup(-(-A6),$B$1:$B$10,$C$1:$C$10)&" puluh ",if(and(-(-A6)=1,-(-A7)=0),lookup(-(-A6),$B$1:$B$10,$C$1:$C$10)&" puluh ","")),"satu puluh","sepuluh")		Konversi Trilyun2
A29	=if(and(-(-A6)=1,-(-A7)=0),"",if(and(-(-A6)=1,-(-A7)=1)," sebelas ",if(-(-A6)=1,lookup(-(-A7),$B$1:$B$10,$C$1:$C$10)&" belas ",lookup(-(-A7),$B$1:$B$10,$C$1:$C$10))))	enam	Konversi Trilyun3
A30	=substitute(if(-(-A9)>0,lookup(-(-A9),$B$1:$B$10,$C$1:$C$10)&" ratus ",""),"satu ratus","seratus")	delapan ratus	Konversi Milyar1
A31	=substitute(if(-(-A10)>1,lookup(-(-A10),$B$1:$B$10,$C$1:$C$10)&" puluh ",if(and(-(-A10)=1,-(-A11)=0),lookup(-(-A10),$B$1:$B$10,$C$1:$C$10)&" puluh ","")),"satu puluh","sepuluh")	tujuh puluh	Konversi Milyar2
A32	=if(and(-(-A10)=1,-(-A11)=0),"",if(and(-(-A10)=1,-(-A11)=1)," sebelas ",if(-(-A10)=1,lookup(-(-A11),$B$1:$B$10,$C$1:$C$10)&" belas ",lookup(-(-A11),$B$1:$B$10,$C$1:$C$10))))	enam	Konversi Milyar3
A33	=substitute(if(-(-A13)>0,lookup(-(-A13),$B$1:$B$10,$C$1:$C$10)&" ratus ",""),"satu ratus","seratus")	lima ratus	Konversi Juta1
A34	=substitute(if(-(-A14)>1,lookup(-(-A14),$B$1:$B$10,$C$1:$C$10)&" puluh ",if(and(-(-A14)=1,-(-A15)=0),lookup(-(-A14),$B$1:$B$10,$C$1:$C$10)&" puluh ","")),"satu puluh","sepuluh")	dua puluh	Konversi Juta2
A35	=if(and(-(-A14)=1,-(-A15)=0),"",if(and(-(-A14)=1,-(-A15)=1)," sebelas ",if(-(-A14)=1,lookup(-(-A15),$B$1:$B$10,$C$1:$C$10)&" belas ",lookup(-(-A15),$B$1:$B$10,$C$1:$C$10))))	tujuh	Konversi Juta3
A36	=substitute(if(-(-A17)>0,lookup(-(-A17),$B$1:$B$10,$C$1:$C$10)&" ratus ",""),"satu ratus","seratus")	lima ratus	Konversi Ribu1
A37	=substitute(if(-(-A18)>1,lookup(-(-A18),$B$1:$B$10,$C$1:$C$10)&" puluh ",if(and(-(-A18)=1,-(-A19)=0),lookup(-(-A18),$B$1:$B$10,$C$1:$C$10)&" puluh ","")),"satu puluh","sepuluh")		Konversi Ribu2
A38	=if(and(-(-A18)=1,-(-A19)=0),"",if(and(-(-A18)=1,-(-A19)=1)," sebelas ",if(-(-A18)=1,lookup(-(-A19),$B$1:$B$10,$C$1:$C$10)&" belas ",lookup(-(-A19),$B$1:$B$10,$C$1:$C$10))))	delapan belas	Konversi Ribu3
A39	=substitute(if(-(-A21)>0,lookup(-(-A21),$B$1:$B$10,$C$1:$C$10)&" ratus ",""),"satu ratus","seratus")	tujuh ratus	Konversi Satuan1
A40	=substitute(if(-(-A22)>1,lookup(-(-A22),$B$1:$B$10,$C$1:$C$10)&" puluh ",if(and(-(-A22)=1,-(-A23)=0),lookup(-(-A22),$B$1:$B$10,$C$1:$C$10)&" puluh ","")),"satu puluh","sepuluh")	enam puluh	Konversi Satuan2
A41	=if(and(-(-A22)=1,-(-A23)=0),"",if(and(-(-A22)=1,-(-A23)=1)," sebelas ",if(-(-A22)=1,lookup(-(-A23),$B$1:$B$10,$C$1:$C$10)&" belas ",lookup(-(-A23),$B$1:$B$10,$C$1:$C$10))))	delapan	Konversi Satuan3
A42	=substitute(if(-(-A25)>1,lookup(-(-A25),$B$1:$B$10,$C$1:$C$10)&" puluh ",if(and(-(-A25)=1,-(-A26)=0),lookup(-(-A25),$B$1:$B$10,$C$1:$C$10)&" puluh ","")),"satu puluh","sepuluh")	lima puluh	Konversi Pecahan1
A43	=if(and(-(-A25)=1,-(-A26)=0),"",if(and(-(-A25)=1,-(-A26)=1)," sebelas ",if(-(-A25)=1,lookup(-(-A26),$B$1:$B$10,$C$1:$C$10)&" belas ",lookup(-(-A26),$B$1:$B$10,$C$1:$C$10))))	enam	Konversi Pecahan2
A44	=if(-(-A5)+-(-A6)+-(-A7)>0,A27&A28&A29&" triliun ","")	enam triliun	Konversi Trilyun
A45	=if(-(-A9)+-(-A10)+-(-A11)>0,A30&A31&A32&" milyar ","")	delapan ratus tujuh puluh enam milyar	Konversi Milyar
A46	=if(-(-A13)+-(-A14)+-(-A15)>0,A33&A34&A35&" juta ","")	lima ratus dua puluh tujuh juta	Konversi Juta
A47	=if(-(-A16)=1," seribu ",if(-(-A17)+-(-A18)+-(-A19)>0,A36&A37&A38&" ribu ",""))	lima ratus delapan belas ribu	Konversi Ribu
A48	=if(-(-A21)+-(-A22)+-(-A23)>0,A39&A40&A41,"")	tujuh ratus enam puluh delapan	Konversi Satuan
A49	=substitute(if(-(-A25)+-(-A26)>0,A42&A43,""),"-","")	lima puluh enam	Konversi Pecahan
A50	=if(-(-A2)=0,"nol",if(-(-A24)=0,substitute(substitute(A44&A45&A46&A47&A48,"-","")," "," "),if(and(-(-A3)=0,-(-A24)>0),"nol koma "&substitute(substitute(A49,"- ","")," "," "),substitute(substitute(A44&A45&A46&A47&A48,"-","")," "," ")&" koma "&A49)))	enam triliun delapan ratus tujuh puluh enam milyar lima ratus dua puluh tujuh juta lima ratus delapan belas ribu tujuh ratus enam puluh delapan koma lima puluh enam	Terbilang - Final
A51	=if(-(-A2)=0,"nol rupiah",if(-(-A24)=0,substitute(substitute(A44&A45&A46&A47&A48,"-","")," "," ")&" rupiah",if(and(-(-A3)=0,-(-A24)>0),substitute(substitute(A49,"- ","")," "," ")&" sen",substitute(substitute(A44&A45&A46&A47&A48,"-","")," "," ")&" rupiah dan "&A49&" sen")))	enam triliun delapan ratus tujuh puluh enam milyar lima ratus dua puluh tujuh juta lima ratus delapan belas ribu tujuh ratus enam puluh delapan rupiah dan lima puluh enam sen	Terbilang - Rupiah

Pada saat proses penyalinan rumus / formula ke lembar kerja, biasanya diikuti dengan penambahan tanda kutip tunggal (untuk penanda teks), maka harap dibuang tanda kutip tersebut agar formula di atas dapat bekerja.

---

From Nothing to Nothing
10/3/2015