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

Memisahkan Text Per Huruf dan Karakter

Masih terkait otak-atik data text, pada kesempatan ini akan dibahas bagaimana memisahkan sebuah text menjadi huruf atau karakter penyusunnya dalam beberapa sel yang terpisah. Kasus seperti ini mungkin pernah anda alami pada saat pengisian sebuah form. Selain menuliskan text secara kontinyu terkadang sebuah form isian meminta kita untuk menuliskan data dalam kotak-kotak kecil yang disediakan, misalnya untuk menuliskan data nama, alamat, tanggal lahir, dan sebagainya. Di excel, salah satu cara untuk memisahkan text menjadi per huruf adalah menggunakan rumus, diantaranya: rumus kombinasi fungsi MID dan COLUMNS.

Contoh Soal.

Dalam sebuah lembar kerja excel sudah disediakan data NAMA, ALAMAT, dan KOTA. Selain itu juga sudah disediakan kotak-kotak kecil berupa cell excel yang diformat lebar kolom yang lebih sempit untuk menampung satu huruf atau satu karakter per kotak sel. Tugas selanjutnya adalah bagaimana membuat rumus untuk memisahkan text nama, alamat dan kota kedalam kotak-kotak kecil tersebut, satu huruf per kotak.

Perhatikan gambar contoh soal di bawah ini.

Rumus Excel Untuk Memisahkan Text Menjadi Per Huruf.

Sebagaimana sudah disinggung pada bagian pembuka, salah satu cara untuk memisakan text menjadi per huruf adalah menggunakan rumus yang merupakan kombinasi dari fungsi MID dan COLUMNS.

• Fungsi MID berguna untuk mengambil karakter pada nomor urut tertentu dari text yang ditentukan
• Fungsi COLUMNS berguna untuk menghitung banyaknya kolom pada range terpilih. Jumlah kolom 1, 2, 3 dan seterusnya bisa dimanfaatkan untuk menentukan nomor urut karakter yang selanjutnya diolah oleh fungsi MID.

Secara general, rumus untuk memisahkan text menjadi perhuruf adalah sebagai berikut:

=MID($sel1,COLUMNS($sel1:cell2),1)

Dimana sel 1 adalah cell dimana lokasi text lengkap berada. Sel1 dan sel2 mereferensikan cell yang sama pada kotak pertama. Sel 1 harus menggunakan tanda dolar pada bagiah kode kolom sehingga tidak berubah pada saat formula di copy ke sebelah kanan. Sel2 akan mereferensikan kolom dengan bergesar satu langkah ke kanan setiap kali kita mengcopy rumus ke sebelah kanan.

Jika diterapkan pada contoh soal, maka rumus pemecah text menjadi perhuruf dapat kita tuliskan pada kotak cell isian paling kiri, yaitu cell D2, dengan rumus sebagai berikut:

=MID($B2,COLUMNS($B2:B2),1)

Selanjutnya rumus dapat di copy ke sel berikutnya sehingga semua huruf dan karakter komponen text dapat diambil dan diletakan pada kotak sel yang berbeda, seperti terlihat pada screenshot di bawah ini:

Cara Kerja Rumus:

Penting diperhatikan penggunaan tanda dolar ($). Tanda tersebut berguna untuk menjadikan referensi absolute, artinya referensi tidak berubah pada saat rumus dicopy dari sebuah sel ke lokasi sel lainnya. Karena yang perlu dikunci hanya kolomnya saja maka tanda dolar diletakan sebelum kode kolom (dalam contoh kolom B)

Perhatikan bagian rumus yang dihighlight  =MID($B2,COLUMNS($B2:B2),1)

Anggaplah kita hanya mengambil bagian yang di highlight saja COLUMNS($B2:B2) dan rumus tersebut terletak di sel A2. Jika rumus di copy sel di sebelahnya di kanan, maka referensi $B2:B2 akan berubah menjadi $B2:C2 demikian seterusnya jika rumus dicopy ke sel berikutnya di sebelah kanan:

Fungsi COLUMNS ini digunakan untuk menghitung jumlah kolom dari referensi terpilih.

COLUMNS($B2:B2) = 1 

COLUMNS($B2:C2) = 2

COLUMNS($B2:D2) = 3

COLUMNS($B2:E2) = 4

Bilangan Jumlah kolom yang dihasilkan oleh fungsi COLUMNS selanjutnya dimanfaatkan oleh fungsi MID untuk menentukan nomor urut huruf dan karakter dari text. Setiap kotak akan mendapatkan jumlah kolom yang berbeda yang meningkat satu tingkat semakin ke kanan.

Rumus di cell D2  =MID($B2,COLUMNS($B2:B2),1) =MID($B2,1,1)   

Rumus di cell E2  =MID($B2,COLUMNS($B2:C2),1) =MID($B2,2,1)   

Rumus di cell F2  =MID($B2,COLUMNS($B2:D2),1) =MID($B2,3,1)   

Rumus di cell G2  =MID($B2,COLUMNS($B2:E2),1) =MID($B2,4,1)   

Dan seterusnya.

Jika referensi $B2 kita ganti dengan text "UNYIL CUPLIS" maka deret rumus akan menjadi seperti ini

Rumus di Cell D2  =MID("UNYIL CUPLIS",1,1)  = "U"   

Rumus di Cell E2  =MID("UNYIL CUPLIS",2,1)  = "N"

Rumus di Cell F2  =MID("UNYIL CUPLIS",3,1)  = "Y"

Rumus di Cell G2  =MID("UNYIL CUPLIS",4,1)  = "I"

Dan seterusnya sehingga setiap kotak berisi satu huruf atau satu karakter sesuai nomor urut karakter tersebut dari kiri ke kanan.

Demikian pembahasan mengenai rumus untuk memisahkan text menjadi per huruf atau karakter dalam kotak sel yang terpisah, Semoga bermanfaat.

Artikel terkait:

• Menghitung Jumlah Kata
• Menghitung Jumlah Huruf
• Memisahkan Nama Depan dan Belakang
• Menggabungkan 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

Menghitung Total Jumlah Huruf

Tehnik olah data ber-type text di excel sangat menarik untuk dipelajari. Salah satunya adalah bagaimana menghitung total jumlah huruf atau karakter. Dengan cukup menggunakan rumus yang sederhana, kita bisa menghitung ada berapa banyak huruf, baik yang terletak dalam satu sel, maupun dalam range atau banyak sel. Fungsi yang dapat digunakan untuk keperluan ini adalah fungsi LEN dan SUMPRODUCT.

Contoh Soal:

List text sudah tersedia pada range A2:B5, kemudian kita diminta untuk menghitung:

1. Jumlah huruf pada masing-masing baris sel A2,A3,A4 dan A5:

2. Total jumlah huruf pada range A2:B5

Perhatikan gambar contoh soal berikut ini:

Catatan: disini kita menggunakan istilah huruf, meskipun sebenarnya yang kita hitung adalah jumlah karakter, maksudnya supaya lebih familiar di telinga. Ini berarti contoh rumus yang akan dibahas juga pada kenyataannya akan menghitung semua karakter, bukan hanya alfabet, tetapi juga termasuk angka dan simbol, termasuk juga tanda spasi, pemisah baris (line break) dan lain sebagainya.

Rumus 1:  Menghitung jumlah huruf pada masing – masing sel

Kita ambil contoh text pertama "Nama Saya Cecep"

Ada berapa huruf dalam string "Nama Saya Cecep" ?

Tanpa menggunakan rumus, kita bisa menghitungnya satu per satu huruf secara manual:

1 = N

2 = a

3 = m

Dan seterusnya, sehingga dapat diketahui jumlah huruf = 15

Tentu saja, cara diatas bukanlah yang kita kehendaki. Karena kita ingin perhitungan cepat menggunakan rumus. Rumus yang digunakan sangat simple. Cukup menggunakan satu fungsi saja, yaitu fungsi LEN.

LEN adalah rumus siap pakai di excel yang digunakan untuk menghitung total jumlah huruf atau karakter yang menyusun sebuah string. Fungsi ini dituliskan dengan syntax:

=LEN(text)

Dengan menggunakan contoh text "Nama Saya Cecep", maka perhitungan jumlah huruf dalam text tersebut dapat dituliskan sebagai berikut:

=LEN("Nama Saya Cecep")

Rumus tersebut akan menghasilkan bilangan 15.

Jika text diganti dengan referensi A2 (lihat contoh tabel soal). Maka rumusmnya menjadi:

=LEN(A2)

Anggaplah rumus tersebut diketikan di sel B2, maka rumus dapat dicopy ke sel dibawahnya untuk mendapatkan jumlah bilangan dari text lainnya, seperti terlihat dalam screenshot di bawah ini.

Rumus 2:  Menghitung total jumlah huruf dari  semua text dalam range A2:A5

Untuk menghitung total huruf dalam range, sebenarnya kita bisa menjumlahkan hasil masing-masing bilangan yang merupakan hasil dari rumus pertama, menggunakan fungsi SUM.

Karena hasil dari rumus jenis pertama terletak di range B2:B5 , maka rumus penjumlahannya dapat dituliskan sebagai berikut:

=SUM(B2:B5)

Seperti terlihat dalam gambar di bawah ini.

Cara di atas, cukup mudah dan saya kira fungsi SUM sudah sangat familiar bagi kebanyakan pengguna excel.

Selanjutnya mari kita coba cara lain, dimana kita bisa menghitung jumlah huruf dari semua text dalam range tanpa harus terlebih dahulu menghitung huruf masing-masing sel.

Adapun rumus yang digunakan merupakan gabungan dari fungsi SUMPRODUCT dan LEN. Rumus ini secara umum dapat dituliskan seperti berikut:

=SUMPRODUCT(LEN(range))

Jika kita terapkan pada contoh tabel soal yang kita gunakan, karena data text terletak pada range A2:A5, maka rumus untuk menghitung total huruf dapat dituliskan seperti ini:

=SUMPRODUCT(LEN(A2:A5))

Hasilnya, mungkin akan nampak seperti gambar berikut:

Cara Kerja Rumus Perhitungan Total Huruf

Sebagaimana kita ketahui, fungsi SUMPRODUCT bekerja pada array data. Fungsi ini bekerja dengan mengalikan komponen-komponen array, kemudian menjumlah hasilnya. 

Dalam kasus contoh soal yang sedang kita bahas,  yang berperan sebagai komponen array adalah masing-masing bilangan yang dihasilkan oleh fungsi LEN atas masing masing text dalam range A2:A5.

Supaya lebih jelas mari kita pecahkan fungsi LEN menjadi array dengan cara seperti terlihat dalam gambar di bawah ini:

Seleksi  bagian rumus LEN(A2:A5), kemudian tekan tombol F9, maka kita bisa amati bahwa fungsi LEN terebut berubah menjadi array {15;25;27;25} yang merupakan kumpulan bilangan hasil perhitungan jumlah huruf masing-masing text.

Pada akhirnya, tugas Fungsi SUMPRODUCT menjumlahkan masing-masing komponen array dimaksud.

=15 + 25 + 27 + 25

=92

---

Cukup sampai disini pembahasan contoh rumus untuk menghitung total jumlah huruf atau karakter, baik jumlah huruf dalam satu sel saja maupun jumlah huruf dalam range.

Semoga bermanfaat.

Artikel terkait:

Menggabungkan Nama Depan dan Belakang

Memisahkan Nama Depan dan Belakang

Cara Mudah Memahami Rumus SUMPRODUCT