Konversi Nomor Urut Kolom Menjadi Huruf

Pada artikel sebelumnya sudah dijelaskan mengenai contoh rumus untuk konversi huruf kolom menjadi angka atau nomor urut kolom menggunakan kombinasi fungsi COLUMN dan INDIRECT. Nah, dalam kesempata ini akan dijelaskan proses sebaliknya yaitu konversi nomor urut kolom menjadi huruf. Untuk melakukan tugas ini setidaknya diperlukan kombinasi dua fungsi yaitu fungsi ADDRESS dan SUBSTITUTE. 

Contoh Soal.

Tersedia sebuah tabel terdiri atas 2 kolom. Kolom pertama berisi data type numerik yang mewakili nomor urut kolom.  Sedangkan kolom ke-2 berisi data type text yang mewakili huruf kolom. Data – data pada kolom ke-2 harus diformulasikan dengan mengambil referensi dari kolom pertama. Gambaran tabel dapat dilihat pada screenshot di bawah ini.

Rumus apa  yang dapat diterapkan pada kolom B?

Setidaknya ada 2 fungsi yang diperlukan untuk menyusun formula untuk konversi angka nomor urut kolom menjadi  huruf. Kedua fungsi tersebut adalah SUBSTITUTE dan ADDRESS. 

Fungsi address digunakan untuk membuat text alamat sel sesuai parameter baris dan kolom dalam fungsi tersebut.

Secara umum, syntax fungsi ADDRESS dapat dituliskan sebagai berikut.

=ADDRESS(nomorBaris, nomorKolom, [nomorABS],[a1],[textSheet])

Ada 5 argument fungsi ADDRESS, tetapi untuk keperluan konversi nomor kolom menjadi text hanya diperlukan parameter nomorBaris, nomorKolom dan nomorABS saja

nomorBaris dan nomorKolom adalah nomor urut baris/kolom sedangkan nomorABS adalah angka yang menunjukan type referensi absolute atau relative.

• nomorABS = 1 ⇒ row dan kolom absolute
• nomorABS = 2 ⇒ hanya baris yang absolute
• nomorABS = 3 ⇒ hanya kolom yang absolute
• nomorABS = 4 ⇒ referensi relative

Karena tujuan kita adalah konversi nomor urut kolom menjadi huruf, maka hanya parameter nomorKolom yang berubah. Untuk parameter nomorBaris, kita gunakan angka konstan, misalnya 1. Sedangkan untuk nomorABS kita gunakan type 4 supaya text address yang dihasilkan tidak mengandung karakter dolar ($).

Contoh penggunaan fungsi ADDRESS

=ADDRESS(1,1,4) ⇒ hasil = "A1"

=ADDRESS(1,2,4) ⇒ hasil = "B1"

=ADDRESS(1,3,4) ⇒ hasil = "C1"

=ADDRESS(1,4,4) ⇒ hasil = "D1"

Dari contoh rumus diatas kita bisa menarik kesimpulan bahwa dengan merubah parameter nomorKolom (perhatikan parameter ke-2 dalam fungsi ADDRESS) maka kita bisa mendapatkan deret alamat sel dengan referensi kolom sesuai parameter nomorKolom. 

Langkah selanjutnya adalah menghilangkan angka 1 dari text alamat sel hasil rumus ADDRESS. Nah tugas selanjutnya ini akan dikerjakan oleh fungsi SUBSITUTE.

Fungsi SUBSTITUTE berguna untuk menggantikan karakter tertentu dari sebuah text dengan karakter lainnya ataupun dengan karakter kosong. Karena kita ingin menghilangkan karakter yang tidak diperlukan yaitu angka 1 maka tugas fungsi SUBSITUTE di sini adalah untuk mengganti angka 1 dengan string kosong ( "" ).

Contoh Penggunaan Fungsi SUBSTITUTE

=SUBSTITUTE("A1",1,"") ⇒ hasil = "A"

=SUBSTITUTE("B1",1,"") ⇒ hasil = "B"

=SUBSTITUTE("C1",1,"") ⇒ hasil = "C"

=SUBSTITUTE("D1",1,"") ⇒ hasil = "D"

Jika fungsi SUBSTITUTE dikombinasikan dengan fungsi ADDRESS, maka rumusnya akan seperti ini:

=SUBSTITUTE(ADDRESS(1,1,4),1,"") ⇒ hasil = "A"

=SUBSTITUTE(ADDRESS(1,2,4),1,"") ⇒ hasil = "B"

=SUBSTITUTE(ADDRESS(1,3,4),1,"") ⇒ hasil = "C"

=SUBSTITUTE(ADDRESS(1,4,4),1,"") ⇒ hasil = "D"

Sekali lagi perhatikan rumus diatas. Apa yang berubah diantara rumus pertama s.d rumus keempat? Ya. Yang berubah adalah parameter nomorKolom di dalam fungsi ADDRESS. Dalam contoh digunakan parameter 1,2,3 dan 4 yang mewakili kolom A, B, C dan D.

Pola rumus itulah yang dapat kita gunakan untuk konversi nomor urut kolom menjadi huruf.

Dengan menggunakan tabel contoh. Maka kita bisa mengetikan rumus di Sel B2 sebagai berikut:

=SUBSTITUTE(ADDRESS(1,A2,4),1,"")

Copy rumus di atas sampai baris akhir data, sehingga hasilnya akan nampak seperti gambar di bawah ini.

Sampai pada tahap ini, kita sudah berhasil membuat rumus untuk konversi nomor urut kolom menjadi huruf. 

Perhatikan hasil error #VALUE pada baris ke 10. Error tersebut disebabkan nomor urut kolom maksimal di excel versi 2010 adalah 16384 atau kolom XFD, sehingga jika kita menggunakan bilangan diatas nilai tersebut maka fungsi ADDRESS tidak bisa mencreate text alamat cell, alias hasilnya error.

Demikian pembahasan singkat mengenai contoh rumus excel untuk konversi angka nomor urut kolom menjadi huruf. Semoga bermanfaat.

Artikel terkait:

• Konversi Huruf Menjadi Nomor Urut Kolom
• Memisahkan Nama Depan dan Nama Belakang.
• Menggabungkan Nama Depan dan Nama Belakang
• Memecahkan Text Per Karakter