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

Merubah Jenis Huruf Besar dan Kecil

Di excel kita bisa merubah jenis huruf besar dan kecil dengan mudah menggunakan rumus. Baik itu mengubah huruf besar (kapital) menjadi kecil ataupun sebaliknya mengkonversi huruf kecil menjadi besar. Selain itu kita juga bisa menentukan hanya huruf pertama dari setiap kata yang dijadikan huruf besar. Adapun fungsi excel yang dapat digunakan dalam rumus untuk merubah jenis huruf besar/kecil adalah fungsi UPPER, LOWER dan PROPER.

Baiklah, mari kita bahas satu persatu.

1. Fungsi UPPER

Fungsi UPPER seperti tersurat dalam namanya, beguna untuk merubah jenis huruf text menjadi huruf kapital (upper case) semuanya. Adapun syntax fungsi ini dapat dituliskan sebagai berikut:

=UPPER(text)

Dimana text adalah sebuah karakter huruf ataupun sekumpulan karakter dalam susunan tertentu yang membentuk kata ataupun kalimat (baik kalimat yang dapat dimengerti ataupun tidak)

Contoh rumus UPPER

=UPPER("jakarta")  ⇒ Hasil = "JAKARTA"

=UPPER("Jakarta") ⇒ Hasil = "JAKARTA"

=UPPER("jaKArta")  ⇒ Hasil = "JAKARTA"

Dari contoh contoh di atas, dapat diketahui bahwa dengan menggunakan fungsi UPPER, kita bisa menyeragamkan jenis huruf dari text "jakarta", "Jakarta", maupun "jaKArta" menjadi huruf besar semuanya ("JAKARTA") tanpa kecuali.

2. Fungsi LOWER

Fungsi LOWER merupakan kebalikan dari fungsi UPPER. Fungsi ini berguna untuk merubah semua huruf penyusun sebuah text menjadi huruf  kecil (lower case) semuanya tanpa kecuali. Syntax fungsi LOWER sangat sederhana, hanya diperlukan satu argumen text, dan dapat dituliskan sebagai berikut:

=LOWER(text)

Contoh rumus LOWER

=LOWER("BANDUNG")  ⇒ Hasil = "bandung"

=LOWER("Bandung")  ⇒ Hasil = "bandung"

=LOWER("BanDUNG")  ⇒ Hasil = "bandung"

Dapat kita lihat dari contoh di atas: fungsi LOWER selalu menghasilkan text "bandung" dimana semua huruf penyusunnya adalah huruf kecil, tidak peduli jenis huruf awalnya bagaimana.

3. Fungsi PROPER

Adapun fungsi PROPER berguna untuk merubah jenis huruf pertama dari setiap kata dalam text menjadi huruf besar atau kapital, sedangkan sisanya berupa huruf kecil.

Contoh rumus PROPER

=PROPER("bandung lautan api")  ⇒ Hasil = "Bandung Lautan Api"

=PROPER("BANDUNG LAUTAN API")  ⇒ Hasil = "Bandung Lautan Api"

=PROPER("bandung LAUTan API")  ⇒ Hasil = "Bandung Lautan Api"

Contoh rumus diatas, membuktikan bahwa hasil dari rumus PROPER adalah berupa text dimana setiap huruf pertama pada setiap kata berupa huruf kapital / besar.

Masalah rumus PROPER.

Salah satu masalah rumus PROPER adalah pada saat mengolah sebuah text nama yang memiliki tanda petik satu di tengahnya, seperti nur'aini, ma'ruf, sema'un dan sebagainya.

Misalnya kita ingin merubah kata "nur'aini"  menjadi "Nur'aini". Kemudian dicobalah fungsi PROPER

=PROPER("nur'aini") ⇒  Hasilnya =  "Nur'Aini"

Perhatikan hasil "Nur'Aini" (huruf "A" setelah tanda petik berupa huruf kapital, padahal kita menginginkan hasil "Nur'aini" dimana huruf "a" setelah tanda petik harusnya tetap huruf kecil.

Untuk mengatasi kasus seperti ini kita bisa menggunakan bantuan fungsi SUBSTITUTE. Adapun rumusnya secara umum dapat dituliskan sebagai berikut:

=SUBSTITUTE(PROPER(SUBSTITUTE(text,"'","xxx")),"xxx","'")

Jika diterapkan untuk text = "nur'aini" , maka rumusnya menjadi:

=SUBSTITUTE(PROPER(SUBSTITUTE("nur'aini","'","xxx")),"xxx","'")

Rumus ini akan lebih berguna jika menggunakan referensi range, alih-alih menuliskan text langsung pada rumus. 

Misalnya jika text "nur'aini" ada di sel A1, maka rumus dapat dituliskan sebagai berikut:

=SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","xxx")),"xxx","'")

Perhatikan penggunaan string "xxx". String tersebut hanya sebagai contoh saja, kita sebenarnya bisa menggunakan string apa saja yang diasumsikan tidak mungkin ada dalam text yang kita olah. String ini berguna untuk menggantikan sementara tanda petik satu (‘) sebelum diolah oleh fungsi PROPER. Setelah diproses oleh PROPER, kemudian tanda petik satu dikembalikan lagi menggantikan "xxx". 

Proses menggantikan tanda petik satu  menjadi "'" menjadi "xxx"  maupun sebaliknya ini menggunakan fungsi SUBSTITUTE, sehingga diperlukan dua fungsi SUBSTITUTE yang bekerja sebelum dan sesudah fungsi PROPER.

Demikain pembahasan singkat mengenai contoh rumus excel untuk merubah jenis huruf besar dan kecil di Excel menggunakan fungsi UPPER, LOWER, dan PROPER, serta bantuan fungsi SUBSTITUTE untuk mengatasi permasalahan dalam rumus PROPER.

Semoga bermanfaat.

Artikel terkait:

• Menghilangkan Spasi Berlebih
• Menghitung Jumlah Kata Tertentu dalam Cell dan Range
• Menghitung Total Jumlah Kata
• Memisahkan Nama Depan dan Belakang

Menghilangkan Spasi Berlebih

Menghapus spasi berlebih di excel terkadang gampang-gampang susah. Gampang jika sudah tahu caranya. Sangat susah kalau tidak tau caranya dan kemudian mencoba menghilangkan spasi berlebih secara manual, menghapusnya satu persatu. Pusing kalau sedikit tahu rumus untuk menghapus spasi tapi belum begitu mengerti konsepnya. Bagi kebanyakan user yang sudah agak tahu cara menghapus menghilangkan spasi berlebih adalah menggunakan fungsi TRIM. Namun ternyata itu saja belum tentu cukup. Ada beberapa kasus dimana menghapus extra spasi menggunakan fungsi TRIM tidak memberikan hasil yang diharapkan.

Contoh-contoh rumus dalam bahasan ini menggunakan parameter text secara langsung di rumus tanpa menggunakan referensi sel, supaya mudah membuat gambarannya. Selain itu, karena sulit menggambarkan jenis spasi dalam screenshot excel, maka sepertinya screenshot tidak perlu ditampilkan.

Fungsi TRIM

Fungsi ini secara umum digunakan untuk menghapus spasi berlebih yang ada di awal kata, di akhir kata dan antara dua kata sehingga hanya menyisakan satu spasi di antara dua kata. Yang lainnya di hapus.

Fungsi TRIM dapat dituliskan sebagai berikut:

=TRIM(text)

Dimana text merupakan string text yang akan dihapus spasi extra-nya.

Misalnya: text  "   AKU     ANAK    SEHAT      " merupakan contoh text dimana ada kelebihan spasi, baik di awal, di antara dua kata, maupun di akhir kalimat

Dengan menggunakan fungsi TRIM, maka kita bisa menghilangkan kelebihan spasi tersebut:

=TRIM("   AKU     ANAK    SEHAT      ")

Dan hasilnya adalah "AKU ANAK SEHAT" tanpa tanda spasi.

Untuk mengecek jumlah karakter yang normal dari text "AKU ANAK SEHAT" , silahkan gunakan fungsi LEN

=LEN("AKU ANAK SEHAT")

Dan hasilnya adalah 14  (catat jumlah ini untuk keperluan pengecekan pada contoh berikutnya)

Catatan: dalam pembahasan ini digunakan tanda petik 2 ( " ). Gunakan tanda tersebut ketika mengambil contoh rumus, sedangkan ketika membaca pembahasan, tanda petik tersebut tidak dihitung dan jangan diambil sebagai bagian dari kalimat.

Mengatasi Masalah Fungsi TRIM tidak Bekerja Sesuai Harapan.

Seperti disampaikan pada bagian pembuka, terkadang penggunaan fungsi TRIM saja tidak cukup untuk menyelesaikan masalah kelebihan extra spasi.

Contoh: copykan rumus berikut ke sebuah sel dalam lembar kerja excel.

=TRIM("   AKU     ANAK    SEHAT      ")

Dan ternyata hasilnya adalah: "  AKU   ANAK    SEHAT   ", masih ada spasi extra baik di awal, di akhir maupun di antara dua kata.

Kemudian kalau di cek menggunakan fungsi LEN maka jumlah karakter hasil rumus trim tersebut adalah:

=LEN(TRIM("   AKU     ANAK    SEHAT      "))

=24

Padahal jumlah karakter text ("AKU ANAK SEHAT" yang normal (tanpa spasi berlebih) seharusnya 14 saja. 

Pasti ada karakter spasi yang belum terhapus.

Apa penyebabnya?

Silahkan dicoba dengan cara mencopy langsung contoh rumus ini (jangan diketik):

• Rumus 1:   =CODE(" ")
• Rumus 2:   =CODE(" ")

Dan bagaimana hasilnya?

Ternyata rumus 1 dan rumus 2 meskipun kenapakannya sama, akan tetapi memberikan hasil yang berbeda. Rumus 1 menghasilkan bilangan 32, sedangkan rumus 2 menghasilkan bilangan 160.

Nah disini letak permasalahannya. Ternyata fungsi TRIM hanya bisa bekerja menghapus spasi yang memiliki kode 32, namun tidak mempan terhadap spasi ber-code 160.

Bagaimana Solusinya?

Untuk mengatasi masalah tersebut kita bisa menggunakan bantuan fungsi SUBSTITUTE untuk menghapus karakter spasi ber code 160. Sehingga contoh rumus TRIM yang terakhir di atas dapat kita rubah menjadi:

=TRIM(SUBSTITUTE("   AKU     ANAK    SEHAT      ",CHAR(160),""))

Hasilnya 14  "AKU ANAK SEHAT", artinya rumus bekerja dengan baik, karena semua extra spasi sudah dihilangkan.

Untuk mengecek apakah rumus sudah bekerja dengan benar, silahkan di cek menggunakan fungsi LEN

=LEN(TRIM(SUBSTITUTE("   AKU     ANAK    SEHAT      ",CHAR(160),"")))

Hasilnya adalah 14, terbukti rumus bekerja sesuai harapan karena jumlah karakter pada text "AKU ANAK SEHAT" yang normal (tanpa spasi berlebih) adalah 14 seperti yang sudah dijelaskan pada bagian atas.

Demikian pembahasan singkat bagaimana menghapus spasi berlebih pada excel. Untuk praktikalnya anda bisa menggunakan referensi sel yang berisi text. 

Semoga bermanfaat.

Artikel terkait: 

• Menghitung Jumlah Kata Tertentu
• Menghitung Jumlah Huruf atau Karakter Tertentu
• Memisahkan Nama Depan dan Belakang
• Menghitung Total Jumlah Kata

Menghitung Jumlah Kata Tertentu Pada Cell dan Range

Masih terkait dengan pengolahan data text, dalam kesempatan ini kita akan membahas contoh rumus untuk menghitung jumlah kata tertentu dalam sebuah sel dan range. Istilah “kata” disini lebih tepatnya disebut sub string, yaitu sekumpulan karakter dalam susunan tertentu. Untuk menghitung jumlah kata tertentu diperlukan bantuan beberapa fungsi excel. Secara umum fungsi yang paling diperlukan adalah LEN dan SUBSTITUTE. Bantuan fungsi UPPER atau LOWER digunakan jika kita hendak menghitung jumlah kata tanpa mempedulikan huruf besar maupun kecil. Sedangkan fungsi SUMPRODUCT diperlukan untuk menghitung jumlah kata tertentu sekaligus dalam sebuah range.

Contoh Soal.

Anggaplah sudah tersedia sejumlah text kalimat. Masing-masing kalimat terletak dalam sel A2, A3 dan A4, atau terletak dalam rangel A2:A4. Kemudian kita hendak menghitung ada berapa sich jumlah text "Kota" dalam sel-sel tersebut. 

Contoh kasus soal diperlihatkan dalam gambar berikut:

Untuk menjawab soal diatas perlu diperhatikan beberapa hal, antara lain:

1. Apakah jumlah kata tertentu akan yang dihitung terletak dalam satu sel atau dalam range. 
2. Apakah kata bersifat case sensitive atau tidak.

Selanjutnya mari kita bahas dengan menampilkan contoh rumus.

Menghitung jumlah kata tertentu dalam sel dengan membedakan huruf besar dan kecil (case sensitive)

Rumus umum

=(LEN(sel)-LEN(SUBSTITUTE(sel,kataDihitung,"")))/LEN(kataDihitung)

Formula diatas dapat diterjemahkan kira-kira begini: Menghitung selisih antara jumlah karakter text penuh dalam sebuah sel dengan jumlah karakter text yang sama namun yang sudah dihilangkan kata kataDihitung. Kemudian membagi hasilnya dengan jumlah karakter dalam kataDihitung.

Parameter kataDihitung bisa kita definisikan sendiri, misalnya kata "Kota".  Sehingga rumus tersebut jika diterapkan dalam contoh soal diatas maka dapat dituliskan sebagai berikut:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"Kota","")))/LEN("Kota")

Berikut contoh penerapannya dalam spreadsheet

Perhatikan hasil yang diperoleh dari rumus tersebut. Rumus perhitungan jumlah kata "Kota" bersifat case sensitive, artinya membedakan huruf besar dan kecil. Oleh karena itu meskipun di sel A2 (text "Kota Bandung adalah Ibu kota Periangan") terdapat kata "Kota" (huruf pertama kapital) dan "kota" (huruf kecil semua), akan tetapi yang terhitung hanya kata "Kota" (huruf pertama besar) sehingga hasilnya 1 saja. 

Menghitung jumlah kata tertentu dalam sel tanpa membedakan huruf besar dan kecil 

Rumus umum:

=(LEN(sel)-LEN(SUBSTITUTE(UPPER(sel),kataDihitung_besar,""))) /LEN(kataDihitung)

Dengan kata lain, rumus di atas dapat dibaca seperti ini: Menghitung selisih antara jumlah karakter text dalam sebuah sel  dengan jumlah karakter text tersebut yang dirubah hurufnya menyadi huruf besar semua dan sudah dikurangi  dengan jumlah karakter kataDihitung dalam huruf besar.

Jika diterapkan dalam contoh soal maka rumus menjadi.

=(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"KOTA","")))/LEN("Kota")

Cara kerja rumus.

• Fungsi LEN ke-1 digunakan untuk menghitung jumlah karakter dalam sel A2. Hasilnya =38
• Fungsi UPPER digunakan untuk merubah text dalam sel A2 menjadi huruf kapital semuanya = "KOTA BANDUNG ADALAH IBU KOTA PERIANGAN"
• Fungsi SUBSTITUTE digunakan untuk menghilangkan text "KOTA" dari "KOTA BANDUNG ADALAH IBU KOTA PERIANGAN" sehingga menjadi " BANDUNG ADALAH IBU  PERIANGAN"
• Fungsi LEN ke-2 digunakan untuk menghitung jumlah karakter dari text " BANDUNG ADALAH IBU  PERIANGAN". Hasilnya = 32
• Fungsi LEN ke-3 digunakan untuk menghitung jumlah karakter dalam string yang dihitung yaitu ("Kota"). Hasilnya = 4
• Langkah Terakhir=  (LEN pertama – LEN kedua)/LEN ketiga  = (38 – 32) / 4 = 2

Selain menggunakan fungsi UPPER, kita juga bisa menggunakan bantuan fungsi LOWER untuk menghitung jumlah kata tanpa mempedulikan huruf besar dan kecil.  Fungsi LOWER berguna untuk merubah semua huruf dari sebuah text menjadi huruf kecil semua. Proses ini kebalikan dari cara kerja fungsi UPPER.

Dengan menggunakan fungsi LOWER, maka formula di atas dapat dimodifikasi menjadi:

=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"kota","")))/LEN("Kota")

Menghitung jumlah kata tertentu dalam range dengan membedakan huruf besar dan kecil (case sensitive)

Untuk keperluan ini kita hanya sedikit memodifikasi rumus sebelumnya yaitu dengan menambahkan fungsi SUMPRODUCT  dan merubah referensi sel menjadi referensi range.

Rumus umum:

=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,kataDihitung,""))) /LEN(kataDihitung))

Berikut contoh terapannya dalam spreasheet:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,"Kota",""))) /LEN("Kota"))

Menghitung jumlah kata tertentu dalam range tanpa membedakan huruf besar dan kecil.

Seperti halnya menghitung jumlah text dalam sel, untuk menghitung jumlah text tertentu dalam sebuah range, kita juga bisa memanfaatkan fungsi UPPER dan LOWER.

Menggunakan fungsi UPPER:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(UPPER(A2:A4),"KOTA",""))) /LEN("Kota"))

Menggunakan fungsi LOWER:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(LOWER(A2:A4),"kota",""))) /LEN("Kota"))

Perhatikan perbedaan bagian rumus yang dihgh light. Ketika kita menggunakan fungsi UPPER, maka kita harus menggunakan text "KOTA" (huruf kapital semua) sebagai parameter text yang disubstitusi. Sebaliknya jika menggunakan fungsi LOWER, maka kita gunakan text "kota" (huruf kecil semua).

Catatan:

Kelemahan dalam rumus-rumus yang disampaikan di atas adalah tidak bisa membedakan apakah kata yang dihitung merupakan bagian atau substring dari string text kata lainnya atau merupakan kata yang berdiri sendiri. Misalnya jika kita menuliskan "Ibu kota" (dengan spasi) ataupun "Ibukota" (tanpa spasi) maka rumus akan memperlakukannya dengan cara yang sama, sehingga akan terhitung jumlah kata "kota" sejumlah 1 buah baik pada text  "Ibu kota" maupun pada text "Ibukota".

Demikian pembahasan singkat mengenai contoh rumus untuk menghitung banyaknya kata tertentu dalam sel dan range, baik dengan pendekatan case sensitive maupun sebaliknya.

Semoga bermanfaat.

Artikel terkait: 

• Menghitung Jumlah Huruf Tertentu Pada Cell dan Range
• Memisahkan Text Per Huruf dan Karakter
• Menghitung Total Jumlah Kata
• Menghitung Total Jumlah Huruf

Menghitung Jumlah Huruf Tertentu Pada Cell dan Range

Bahasan kali ini mengenai Menghitung jumlah huruf atau karakter tertentu, merupakan kelanjutan dari bahasan beberapa waktu yang lalu mengenai contoh rumus untuk menghitung total jumlah jumlah huruf. Untuk menghitung huruf atau karakter tertentu dalam sebuah cell diperlukan keterlibatan beberapa fungsi excel, yaitu Fungsi LEN, Fungsi SUBSTITUTE dan Fungsi UPPER. Sedangkan jika referensi yang kita gunakan berupa range, maka diperlukan tambahan fungsi SUMPRODUCT supaya rumus dapat memberikan hasil yang diharapkan.

Contoh Soal

Di sini sudah disediakan contoh range A2:A4 berisi text nama kota besar. Tugas kita selanjutnya adalah menghitung karakter yang ditentukan, anggaplah huruf “K” (Kapital) sebagai contoh.

• Pertanyaan 1. Bagaimana rumus untuk menghitung huruf "K" pada masing-masing sel A2, A3 dan A4.
• Pertanyaan 2. Bagaimana rumus untuk menghitung total huruf "K" pada range A2:A4.

Mari kita bahas satu persatu:

Menjawab Pertanyaan 1: Gunakan rumus untuk menghitung jumlah karakter tertentu pada sebuah sel

Rumus umum yang dapat digunakan untuk keperluan ini adalah:

=LEN(text)-LEN(SUBSTITUTE(UPPER(text),huruf,""))

Jika parameter text diganti dengan referensi sel A2 dan parameter huruf diganti dengan "K", maka rumus menjadi 

=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"K",""))

Rumus diatas dapat diterjemahkan bahwa banyaknya huruf "K" sama dengan selisih antara jumlah karakter dalam text dengan jumlah karakter dalam text tanpa huruf "K". 

Lalu apa kegunaan fungsi UPPER? 

Fungsi ini bertugas untuk merubah semua huruf menjadi huruf kapital. Ini dimaksudkan supaya semua huruf "K" maupun "k" dapat diproses oleh fungsi SUBSTITUTE, karena fungsi ini bersifat case sensitive. Fungsi SUBSTITUTE sendiri digunakan untuk mengganti huruf "K" (baik huruf kecil maupun kapital) menjadi "" atau menghapusnya supaya dapat diketahui jumlah karakter dalam text tanpa huruf K.

Perhatikan konten sel A2 = "Kota Pekanbaru"

• Fungsi LEN pertama menghitung jumlah karakter dalam text "Kota Pekanbaru" = 14
• Fungsi UPPER merubah "Kota Pekanbaru" menjadi "KOTA PEKANBARU"
• Fungsi SUBSTITUTE menghilangkan huruf "K" dalam text "KOTA PEKANBARU" = "OTA PEANBARU"
• Fungsi LEN ke-2 menghitung jumlah karakter dalam text "OTA PEANBARU" =12
• Jumlah huruf "K" = selisih antara hasil fungsi LEN ke-1 dan LEN ke-2.  Hasilnya = 14-12=2
• Screenshot berikut menggambarkan contoh pererapan rumus dalam lembar kerja excel.

Catatan: jika kita ingin menghitung jumlah huruf secara case sensitif atau membedakan huruf kecil dan besar, maka kita bisa merubah fungsi di atas dengan menghilangkan keterlibatan fungsi UPPER. Dengan demikian, rumus dapat dituliskan sebagai berikut:

=LEN(A2)-LEN(SUBSTITUTE(A2,"K",""))

Rumus di atas akan menghasilkan bilangan 1, karena jumlah huruf "K" (huruf kapital) dalam text "Kota Pekanbaru" hanya ada satu buah, yaitu huruf pertama saja.

Menjawab Pertanyaan 2: Gunakan rumus untuk menghitung jumlah karakter tertentu pada range.

Untuk menghitung jumlah huruf K (besar dan kecil) dalam range tidak terlalu sulit. Kita hanya cukup memodifikasi rumus pertama dengan menambahkan fungsi SUMPRODUCT dan merubah referensi sel menjadi range.

Sehingga rumus dapat dituliskan sebagai berikut:

=SUMPRODUCT(LEN(A2:A4)-LEN(SUBSTITUTE(UPPER(A2:A4),"K","")))

SUMPRODUCT berguna untuk menjumlahkan komponen array yang dihasilkah oleh proses dalam rumus LEN(A2:A4)-LEN(SUBSTITUTE(UPPER(A2:A4),"K","")) yaitu {2;2;1}

=SUMPRODUCT({2;2;1}) = 5

Kenampakan rumus dalam lembar kerja excel mungkin akan seperti ini:

Sama  halnya dengan rumus untuk menghitung jumlah karakter dalam   sel, kita juga dapat menghilangkan peranan fungsi UPPER dalam rumus menghitung jumlah karakter dalam range supaya rumus bersifat case sensitive:

=SUMPRODUCT(LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,"K","")))

Contoh rumus terakhir diatas akan menghasilkan bilangan 3 karena memang yang terhitung hanya huruf "K" Kapital saja.

Demikian pembahasan singkat mengenai contoh rumus untuk menghitung jumlah huruf tertentu, baik dalam sel dan range. Semoga bermanfaat.

Artikel terkait:

• Menghitung Total Jumlah Huruf
• Menghitung Total Jumlah Kata
• Memisahkan Text Per Huruf
• Memisahkan Nama Depan dan Belakang

Menghitung Total Jumlah Kata

Melanjutkan pembahasan sebelumnya mengenai cara menghitung total jumlah huruf, kali ini kita akan membahas bagaimana menghitung total jumlah kata dalam cell dan range. Untuk menyelesaikan tugas ini diperlukan beberapa fungsi, antara lain Fungsi LEN dan SUBSTITUTE jika text berada dalam satu cell. Sedangkan jika text berada dalam range cell, maka diperlukan tambahan fungsi lainnya yaitu SUMPRODUCT. Fungsi TRIM kadang-kadang diperlukan untuk mengatasi extra spasi yang bisa menimbulkan kesalahan perhitungan penjumlahan kata.

Contoh Soal:

Kita gunakan contoh text yang juga digunakan pada artikel Menghitung Jumlah Huruf. Hanya saja digunakan untuk menghitung jumlah kata, seperti terlihat dalam gambar berikut:

Sebelum melanjutkan ke pembahasan rumus, Perlu diperhatikan dahulu asumsi dalam melakukan perhitungan jumlah kata.

• Kata diartikan sebagai susunan kumpulan huruf yang dipisahkan oleh satu spasi dengan kumpulan huruf lainnya. Dalam excel, kata bisa berupa kumpulan huruf yang mempunyai arti atau bisa juga berupa kumpulan huruf tanpa arti. Misalnya kita bisa saja mengetikan text  “abzc rga#r heuxp$r” (tanpa tanda petik). Excel akan mebbaca string “abzc”, “rga#r” dan “heuxp$r” sebagai sebuah kata yang berdiri secara individual.

• Jumlah kata dalam sebuah kalimat sama dengan jumlah spasi ditambah 1. Hal ini juga harus mengasumsikan bahwa hanya ada satu spasi diantara dua kata, dan tidak ada spasi pada awal dan akhir kalimat. Untuk lebih jelasnya, perhatikan gambar di bawah ini:

Dengan asumsi demikian,  maka dapat dibuat deret bilangan seperti di bawah ini:

0 spasi = 1 kata atau tidak ada kata sama sekali

1 spasi = 2 kata

2 spasi = 3 kata

3 spasi = 4 kata

n spasi = n+1 kata

Sehingga dapat disimpulkan bahwa menghitung jumlah kata sama dengan menghitung jumlah spasi kemudian menambahkan satu terhadap hasil perhitungan jumlah spasi.

Baiklah, mari kita lanjutkan pembahasan rumus.

Rumus 1 : Menghitung total jumlah kata dalam satu sel

Pendekatan umum untuk menghitung jumlah kata pada sel excel adalah: 

= Panjang_text – panjang_text_tanpa_spasi + 1

Untuk tugas ini diperlukan bantuan fungsi LEN dan fungsi SUBSTITUTE. Fungsi LEN digunakan untuk menghitung jumlah karakter dalam sebuah text. Fungsi SUBSTITUTE digunakan untuk membuang karakter spasi dari text. Sehingga rumusnya, secara umum dapat dituliskan sebagai berikut:

=LEN(text)-LEN(SUBSTITUTE(text," ",""))+1

Mari kita coba praktekan: Jika text diganti dengan kalimat seperti ("Nama Saya Cecep") maka rumusnya akan seperti ini:

=LEN(("Nama Saya Cecep")-LEN(SUBSTITUTE(("Nama Saya Cecep"," ",""))+1

Perhatikan rumus SUBSTITUTE. Rumus ini akan menghasilkan text baru dimana beberapa karakter diantaranya diganti karakter baru atau ditiadakan. Dalam kasus ini karakter spasi  " " diganti dengan karakter kosong "" atau ditiadakan, sehingga text "Nama Saya Cecep" menjadi "NamaSayaCecep", sehingga rumus tadi menjadi:

=LEN(("Nama Saya Cecep")-LEN("NamaSayaCecep")+1

=15-13+1

=3

Uraian di atas menjelaskan sekilas mengenai cara kerja rumus untuk menghitung jumlah kata dalam sebuah sel. Perhatikan kembali rumus umum untuk menghitung 

=LEN(text)-LEN(SUBSTITUTE(text," ",""))+1

Jika text ditempatkan dalam sebuah referensi sel, anggaplan di sel A2 sesuai tabel contoh soal  di atas. Maka kita dapat menuliskan rumusnya menjadi:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

Rumus tersebut bisa dicopy ke sel bawahnya untuk menghitung total jumlah kata di sel berikutnya, seperti nampak dalam screenshot di bawah ini:

Rumus 2 : Menghitung total jumlah kata dalam range.

Untuk tugas ini diperlukan tambahan fungsi SUMPRODUCT, selain LEN dan SUBSTITUTE.

Rumus umum

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range," ",""))+1)

Kalau anda perhatikan, rumus ke-2 ini hanya sedikit bedanya bila dibandingkan dengan rumus ke-1. Selain tambahan SUMPRODUCT, yang lainnya kita hanya tinggal mengganti parameter text atau referensi sel dengan range.

Apabila range tersebut diganti dengan range A2:A5 sesuai tabel contoh yang digunakan dalam artikel ini maka rumusnya menjadi seperti berikut ini:

=SUMPRODUCT(LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5," ",""))+1)

Dan berikut screenshot contoh penggunaan rumus di atas dalam spreadsheet excel:

Untuk menganalisa cara kerja rumus, silahkan dicoba langkah-langkah berikut:

Seleksi porsi rumus LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5," ",""))+1

Kemudian tekan F9, sehingga akan dihasilkan data array  {3;4;5;4}

Masing-masing komponen array tersebut merupakan jumlah kata pada masing-masing sel. Selanjutnya tugas SUMPRODUCT untuk menjumlahkan semua komponen array tersebut sehingga diperoleh total kata dalam range.

Bagaimana Jika ada Extra Spasi?

Sebagaimana dijelaskan bahwa diasumsikan jumlah kata = jumlah spasi + 1. Dengan asumsi tersebut tentunya rumus akan memberikan hasil yang tidak benar jika terdapat extra spasi atau spasi berlebih, misalnya ada lebih dari satu spasi di antara dua kata, atau ada spasi di awal dan akhir text.

Untuk mengatasi masalah ini, kita bisa menggunakan bantuan fungsi TRIM. Fungsi ini memang sudah didesign untuk menghapus semua spasi, kecuali spasi tunggal diantara dua kata.

Syntax fungsi TRIM sangat sederhana, hanya perlu sebuah parameter text, atau sebuah referensi berisi text.

=TRIM(text)

Terkait pembahasan dalam artikel ini, maka fungsi TRIM dapat dimasukan ke rumus:

Rumus ke-1 : menghitung jumlah kata dalam sel

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

Rumus ke-2 : menghitung jumlah kata dalam range

=SUMPRODUCT(LEN(TRIM(A2:A5))-LEN(SUBSTITUTE(TRIM(A2:A5)," ",""))+1)

Silahkan untuk mencoba kedua rumus tersebut, dan lihat efeknya bila kita menambahkan extra spasi pada sel A2 atau range A2:A5. Kemudian bandingkan dengan hasil kedua rumus tanpa TRIM yang sudah dijelaskan sebelumnya.

Sampai disini pembahasan mengenai contoh rumus untuk menghitung total jumlah kata dalam sel dan range pada excel. Semoga bermanfaat.

Artikel terkait:

• Menghitung Jumlah Huruf
• Memisahkan Nama Depan dan Belakang
• Menggabungkan Nama Depan dan Belakang