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

Menggabungkan Nama Depan dan Belakang

Pada artikel sebelumnya sudah dibahas contoh rumus untuk memisahkan nama depan dan nama belakang. Nah, pada kesempatan ini, kita akan membahas kebalikannya:  Rumus untuk menggabungkan nama depan dan nama belakang. Untuk tujuan tersebut, setidaknya ada dua metode yang dapat digunakan: Yang pertama menggunakan operator “&” atau ampersand, dan yang kedua menggunakan fungsi CONCATENATE.

Contoh Soal.

Sudah tersedia sebuah tabel dalam lembar kerja excel. Tabel tersebut terdiri atas 3 kolom. Kolom ke-1 berisi data nama depan, kolom ke-2 berisi data nama belakang, sedangkan kolom ke-3 berisi nama lengkap. Kolom pertama dan kedua diisi dengan cara diinput manual. Sedangkan kolom ketiga berisi rumus untuk menggabungkan kata yang ada di kolom pertama dan kedua.

Bagaimana rumus yang harus kita buat untuk diletakan di kolom ke-3 atau kolom nama lengkap?

Sebagaimana sudah disinggung pada pembuka, setidaknya ada 2 jenis rumus yang dapat kita gunakan:

Rumus 1: Menggabungkan nama depan dan belakang menggunakan opearator “&” atau ampersand.

Operator “&” atau tanda ampersand bekerja layaknya operator matematika. Hanya saja operator matematika bekerja pada data type bilangan, sedangkan operator “&” bekerja pada data type text. Operator ini digunakan untuk menggabungkan kata, rumus umumnya dapat dituliskan seperti ini:

=text1&text2&text3&…

Artinya text1 akan disambungkan dengan text2, text3 dan seterusnya.

Dalam kaitan penggabungan nama depan dan nama belakang, maka rumus tersebut dapat dituliskan sebagai berikut:

=nama_depan&spasi&nama_belakang

Misalnya:

Nama_depan = Galih

Nama_belakang = Putra

Tanda spasi  harus ada diantara nama depan dan belakang

Maka rumus untuk menggabungkan “Galih”, tanda spasi “ “ dan “Putra” adalah:

="Galih"&" "&"Putra"

Perhatikan: untuk menuliskan nama depan dan belakang secara langsung dalam rumus,  maka nama depan dan belakang tersebut harus didalam tenda petik dua. Jika tidak, maka kemungkinan rumus akan error #NAME?.

Kembali ke contoh tabel, maka rumus yang dapat diketik di kolom ke-3 di sel C2 adalah:

=A2&" "&B2

Selanjutnya rumus tersebut tinggal di copy saja ke sel di bawahnya.

Hasilnya: maka nama depan dan nama belakang sudah dapat digabungkan:

Rumus 2: Menggabungkan nama depan dan belakang menggunakan fungsi CONCATENATE

Cara kedua yang juga cukup mudah sederhana adalah menggunakan fungsi CONCATENATE. Fungsi ini digunakan untuk menggabungkan kata atau text, dengan syntax berikut:

=CONCATENATE(text1,text2,text3,…) 

Untuk kasus penggabungan nama depan dan belakang, text atau string yang harus digabungkan adalah nama_depan, spasi dan nama_belakang. Secara umum dapat dituliskan:

=CONCATENATE(nama_depan,spasi,namabelakang)

Misalnya:

Nama_depan = Budi

Nama_belakang = Cahyadi

Diantara nama depan dan nama belakang harus ada spasi " "

Maka rumus untuk menggabungkan "Budi", tanda spasi " " dan "Cahyadi" adalah:

=CONCATENATE("Budi"," ","Cahyadi")

Jika diterapkan dalam tabel contoh, dimana text nama_depan dan nama_belakang menggunakan referensi sel, maka kita bisa menuliskan rumus di sel C2 sebagai berikut:

=CONCATENATE(A2," ",B2)

Rumus tersebut selanjutnya tinggal dicopy saja ke sel di baris selanjutnya sehingga nama depan dan nama belakang yang ada dikolom pertama dan kedua dapat digabungkan semuanya, seperdi digambarkan dalam screenshot di bawah ini.

Catatan:

Operator “&” dan fungsi CONCATENATE merupakan fitur excel yang digunakan untuk menggabungkan text. Yang dimaksud text disini tidak saja alfabet, tetapi juga termasuk karakter angka,  simbol termasuk tanda spasi yang merupakan karakter yang tidak nampak  bentuknya secara visual. Silahkan dipraktekan penggunaan Operator “&” dan fungsi CONCATENATE untuk menggabungkan text lainnya, bukan hanya nama depan dan belakang  saja.

Demikian pembahasan singkat mengenai rumus untuk menggabungkan nama depan dan nama belakang. Dari dua cara yang dijelaskan di atas silahkan dipilih mana yang lebih nyaman anda menggunakannya. Kedua-duanya sama-sama mudah dan simple.

Semoga bermanfaat.

Barangkali anda juga tertarik untuk mengintip artikel lainya:

Memisahkan Nama Depan dan Belakang

Menghitung Data Unik dengan SUMPRODUCT

Memisahkan Nama Depan dan Belakang

Ada beberapa fitur excel yang dapat digunakan untuk memisahkan nama depan dan nama belakang.  Salah satu di antaranya adalah menggunakan rumus atau formula. Pembahasan kali ini akan mengambil contoh sederhana bagaimana memisahkan nama depan dan belakang menggunakan rumus. Namun diasumsikan bahwa setiap nama terdiri atas 2 kata saja, yaitu nama depan dan nama belakang.

Contoh Soal

Misalnnya di lembar kerja excel sudah tersedia sebuah tabel yang terdiri atas 3 kolom. Kolom pertama berisi nama lengkap, kolom ke-2 berisi data nama depan dan kolom ke-3 berisi data nama belakang. Kolom ke-2 dan ke-3 tidak boleh diketik secara manual, tetapi harus menggunakan rumus yang mengambil referensi dari kolom pertama.

Bentuk tabel contoh dapat dilihat seperti gambar berikut:

Perhatikan gambar di atas pada kolom A atau kolom Nama Lengkap. Kita dapat mengetahui bahwa Kunci untuk memisahkan nama depan dan nama belakang adalah sebuah spasi. Artinya kita harus mengetahui posisi spasi dalam text nama lengkap supaya kita bisa memecah nama tersebut menjadi dua bagian.

Untuk mengetahui posisi spasi dalam text nama lengkap, bisa digunakan fungsi FIND, yang secara umum dapat dituliskan:

 =FIND(spasi,nama_lengkap)

Dimana parameter spasi = tanda spasi, sedangkan nama_lengkap adalah data nama lengkap yang dibangun oleh beberapa karakter dan salah satunya berupa karakter spasi. Untuk rumus praktikalnya dapat dicontohkan sebagai berikut:

=FIND(" ","Andi Rahman")

Rumus di atas akan menghasilkan bilangan 5 yang merupakan posisi nomor urut spasi pada text “Andi Rahman”. Tanda petik menunjukan sebuah string, artinya text yang memang harus dibaca sebuah text. Jika tidak ada tanda petik, maka rumus akan error #NAME? karena text nama akan terbaca sebagai nama sel atau nama range.

Selanjutnya: setelah posisi spasi sudah dapat diketahui, maka kita bisa menentukan text nama depan dengan nama belakang. Sekali lagi sy sampaikan contoh disini diasumsikan nama lengkap terdiri atas dua kata, atau yang dimaksud nama depan adalah satu kata sebelum tanda spasi.

Rumus Untuk Mengambil Nama Depan

Cara 1: Mengambil nama depan menggunakan fungsi MID dan FIND

Kita mulai penjelasannya dari fungsi MID, perhatikan rumus umum berikut:

=MID(nama_lengkap,1,jumlah_karakter_nama_depan)

Rumus tersebut dapat diterjemahkan dalam sebuah kalimat: mengambil bagian text dari string nama_lengkap dimulai dari karakter ke-1 sebanyak jumlah_karakter_nama_depan. Karena jumlah karakter nama depan adalah sama dengan posisi spasi dikurangi 1 maka , rumus tersebut juga dapat dituliskan sebagai berikut:

=MID(nama_lengkap,1, posisi_spasi-1)

Seperti dijelaskan pada bagian awal, posisi_spasi dapat dicari dengan bantuan fungsi FIND, yaitu =FIND(spasi,nama_lengkap), sehingga rumus diatas dapat juga dituliskan sebagai berikut:

=MID(nama_lengkap,1, FIND(spasi,nama_lengkap),-1)

Dan secara praktikal menggunakan contoh tabel kasus di atas, maka rumus dapat dituliskan seperti di bawah ini:

=MID(A2,1,FIND(" ",A2)-1)

Cara 2 : Mengambil nama depan menggunakan fungsi LEFT dan FIND

Secara umum, rumusnya dapat dituliskan begini:

=LEFT(nama_lengkap,jumlah_karakter_nama_depan)

Artinya mengambil text dari nama_lengkap mulai dari sebelah kiri sebanyak jumlah_karakter_nama_depan.

Karena jumlah_karakter_nama_depan juga sama dengan posisi spasi dikurangi 1 yang diperoleh melalui rumus FIND, maka rumusnya juga bisa jadi seperti ini:

=LEFT(nama_lengkap,FIND(" ",nama_lengkap)-1)

Secara praktikal menggunakan contoh tabel yang disediakan di atas, maka rumus dapat dituliskan sebagai berikut:

=LEFT(A2,FIND(" ",A2)-1)

Rumus Untuk Mengambil Nama Belakang:

Cara 1: Mengambill Nama Belakang Menggunakan Rumus MID dan FIND

Rumus umum:

=MID(nama_lengkap,FIND(" ",nama_lengkap)+1,100)

Yang dapat diterjemahkan sebagai berikut: mengambil string yang merupakan bagian dari string nama_lengkap dimulai dari posisi spasi di tambah 1, sejumlah 100 karakter. Posisi spasi dicari menggunakan fungsi FIND. Bilangan 100 mengasumsikan bahwa panjang nama belakang tidak melebihi 100 karakter.

Rumus tersebut dapat dituliskan di lembar kerja excel seperti berikut:

=MID(A2,FIND(" ",A2)+1,100)

Cara 2: Mengambil Nama Belakang Menggunakan Rumus RIGHT, FIND dan LEN

=RIGHT(nama_lengkap,jumlah_karakter_nama_belakang)

Jumlah karakter nama belakang sendiri harus dicari menggunakan rumus yaitu panjang karakter nama lengkap dikurangi posisi spasi. Panjang text nama_lengkap dicari menggunakan fungsi LEN, sedangkan posisi spasi dicari menggunakan fungsi FIND, sehingga rumus akan seperti ini:

=RIGHT(nama_lengkap,LEN(nama_lengkap)-FIND(" ",nama_lengkap))

Secara praktikan menggunakan tabel contoh dapat diketikan dengan rumus berikut 

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Sampai disini saya kira sudah cukup pembahasan mengenai cara memisahkan nama depan dan nama belakang. Ada beberapa alternative yang bisa digunakan, silahkan dicoba.

Terimakasih. 

Baca juga:

Menghitung data antara dua tanggal

Menghitung data antara dua bilangan

Menghitung Data di Antara 2 Bilangan

Salah satu contoh praktis menghitung banyaknya data di antara dua bilangan adalah menghitung jumlah orang yang memiliki umur atau usia pada rentang tertentu. Ada beberapa alternative rumus yang dalam digunakan. 3 contoh rumus diantaranya akan dibahas dalam kesempatan ini. Masing-masing rumus menggunakan fungsi yang berbeda yaitu: COUNTIFS, COUNTIF, dan SUMPRODUCT. 

Contoh Soal:

Untuk contoh soal ini, kita gunakan tabel data yang sangat sederhana saja supaya lebih mudah difahami. Anggaplah ada sebuah tabel yang terdiri atas 2 kolom. Kolom pertama berisi item nama peserta sebuah perlombaan, sedangkan kolom kedua berisi data umur peserta lomba tersebut dalam satuan tahun. Tugas selanjutnya adalah bagaimana menghitung jumlah orang yang berumur antara 15 s.d 20 tahun.

Cara “pertama” yang cukup “disukai” adalah dengan menghitung menggunakan jari, mata dan mulut. Alias mata sedikit menyipit menatap monitor sambil mengerutkan kening, jari menggerakan mouse dan mulut bergumam, menghitung data yang sesuai :  Satu,… dua…, tiga…

Ups, tapi bukan cara itu yang dimaksud. Bayangkan saja jika kita ada memiliki 1000 baris data saja. Betapa lelahnya jika kita harus menghitungnya satu persatu.

Ada 3 contoh rumus yang akan saya tawarkan kepada anda disini (seperti jualan aja yah..)

Cara 1: Menggunakan Rumus COUNTIFS.

Secara general rumusnya dapat dituliskan sebagai berikut:

=COUNTIFS(range_bilangan,kriteria_bawah,range_bilangan,kriteria_atas)

Jika dihubungkan dengan contoh tabel di atas.

• Range_bilangan adalah kolom tabel yaitu range B2:B10
• Kriteria_bawah adalah kriteria komparasi dengan nilai terbawah, karena kita menginginkan bilangan umur minimal 15 tahun, maka dapat dituliskan ">=15"
• Kriteria_atas adalah kriteria komparasi dengan nilai teratas, karena kita menginginkan bilangan umur maksimum 20 tahun, maka dapat dituliskan "<=20"

Pada akhirnya, rumus dapat dituliskan seperti berikut ini:

=COUNTIFS(B2:B10,">=15",B2:B10,"<=20")

Atau terlihat seperti gambar dalam screenshot di bawah ini :

Perhatikan gambar di atas: highlight kuning pada tabel umur dimaksudkan untuk mengecek secara manual apakah rumus sudah bekerja dengan benar sesuai yang diharapkan. Jika kita hitung secara manual maka kita dapatkan 6 orang yang umurnya antara 15 s.d 20 tahun. Hasil yang sama juga diberikan oleh rumus. 

Cara 2: Menggunakan Rumus COUNTIF.

Jika anda masih menggunakan excel versi lama (2003 atau yang lebih tua), anda tidak akan menemukan fungsi COUNTIFS pada excel versi tersebut. Maka sebagai alterntivenya bisa digunakan fungsi COUNTIF (tanpa huruf S di ujung).

Rumus General:

Dalam hal ini menggunakan dua buah fungsi COUNTIF

• COUNTIF ke-1 untuk menghitung Jumlah bilangan yang lebih atau sama dengan batas bawah
• COUNTIF ke-2 untuk menghitung Jumlah bilangan yang lebih dari batas atas

Hasil yang diinginkan nantinya akan berupa pengurangan dari COUNTIF ke-1 dikurangi COUNTIF KE-2.

(COUNTIF ke-1) – (COUNTIF ke-2)

Atau:

=COUNTIF(range_bilangan,kriteria_bawah)-COUNTIF(range_bilangan,kriteria atas)

• Range_bilangan adalah kolom tabel yaitu range B2:B10
• Kriteria_bawah (pada COUNTIF ke-1) adalah kriteria komparasi dengan nilai terbawah, karena kita menginginkan bilangan umur minimal 15 tahun, maka dapat dituliskan ">=15"
• Kriteria_atas (pada COUNTIF ke-2) adalah kriteria komparasi dengan nilai teratas, karena dijadikan sebagai pengurang maka yang harus dihitung oleh COUNTIF ke-2 adalah bilangan umur yang lebih dari 20, atau dituliskan ">20"

Pada akhirnya rumus dapat diketik sebagai berikut:

=COUNTIF(B2:B10,">=15")-COUNTIF(B2:B10,">20")

Cara 3: Menggunakan Rumus SUMPRODUCT

Cara selanjutnya adalah menggunakan fungsi SUMPRODUCT. Jika anda belum begitu familiar dengan fungsi SUMPRODUCT. Silahkan dibaca terlebih dahulu artikel: Cara Mudah Memahami Rumus SUMPRODUCT

Jika sudah, mari kita lanjukan ke contoh kasus menghitung jumlah orang pada range umur 15-20 tahun. Adapun rumusnya secara general dapat dituliskan sebagai berikut:

=SUMPRODUCT(--(range_bilangan>=batas_bawah),--(range_bilangan<= 20))

Atau jika parameter-parameternya diganti dengan referensi dan kritera yang ditetapkan dalam contoh tabel, maka rumusnya menjadi seperti di bawah ini.

=SUMPRODUCT(--(B2:B10>=15),--(B2:B10<=20))

Demikian 3 contoh rumus untuk menghitung jumlah data di antara dua bilangan (a.k.a menghitung jumlah orang dalam rentang usia tertentu). Semoga bermanfaat.

Artikel terkait:

Menghitung Data Antara Dua Tanggal

Cara mudah memahami rumus SUMPRODUCT

Menghitung Data Unik Dengan SUMPRODUCT

Menghitung Data Antara Dua Tanggal

Salah satu cara untuk menghitung banyaknya data antara dua tanggal adalah dengan menggunakan rumus COUNTIFS. Mulai diperkenalkan pada excel 2017, fungsi COUNTIFS ini memiliki keunggulan dibandingkan dari fungsi pendahulunya COUNTIF (Tanpa S). COUNTIFS mampu melakukan penjumlahan bersyarat banyak kriteria dengan cukup satu fungsi saja. Hal ini mustahil dapat dilakukan oleh fungsi COUNTIF. 

Mari kita lihat contoh rumus COUNTIFS untuk menghitung jumlah sel atau data di antara dua tanggal yang ditentukan

Contoh Kasus

Anggaplah kita memiliki list data berupa data tanggal lahir sejumlah orang. Kemudian hendak diketahui ada berapa sih jumlah orang yang lahir diantara tanggal tertentu, Misalnya ditanyakan berapa jumlah orang dalam list yang lahir di antara tanggal 1 Januari 2000 sampai dengan tanggal 31 Desember 2013. Perhatikan screenshot berikut:

Untuk menyelesaikan tugas di atas, kita bisa menggunakan rumus fungsi COUNTIFS, sesuai pembahasan topik fungsi exel kali ini. 

Secara general penulisan rumus IF untuk kasus ini adalah:

=COUNTIFS(rangeTanggal,">="&tanggal_bawah,rangeTanggal,"<="&tanggal_atas)

Contoh Rumus 1 : Menuliskan tanggal langsung dalam rumus.

Untuk mengikutkan tanggal terbawah dan tanggal teratas dalam rumus COUNTIFS, diperlukan bantuan fungsi lainnya. Dalam hal ini kita gunakan fungsi DATE. 

Fungsi DATE akan menghasilkan data tanggal lengkap dengan memasukan parameter year (tahun), month (bulan) dan day (hari)

=DATE(tahun, bulan, hari)

Tahun = bilangan tahun, bulan = nomor urut bulan, hari = nomor urut hari dalam sebulan.

Sehingga untuk menghitung jumlah orang yang lahir dari tanggal 1 Januari 2000 s.d 31 Desember 2018, kita menggunakan fungsi DATE untuk menentukan tanggal terbawah dan tanggal teratas.

• Tanggal terbawah = 1 Januari 2000,  rumusnya =DATE(2000,1,1)
• Tanggal teratas = 31 Januari 2018, rumusnya   =DATE(2003,12,31)

Kemudian rumus tersebut digabung dengan rumus COUNTIFS, sehingga dapat dituliskan seperti berikut:

=COUNTIFS(rangeTanggal,">="&DATE(2000,1,1),rangeTanggal,"<="&DATE(2003,12,31))

Dikarenakan parameter rangeTanggal terletak pada range B2:B10 (lihat contoh tabel di atas), maka kemudian rumus tersebut dapat diketikan seperti di bawah ini.

=COUNTIFS(B2:B10,">="&DATE(2000,1,1),B2:B10,"<="&DATE(2003,12,31))

Penggunaan rumus tersebut di lembar kerja excel digambarkan dalan screenshot di bawah ini

Penting diperhatikan mengenai cara penulisan tanda lebih dari atau sama dengan (">=") dan operator kurang dari atau sama dengan ("=<"). Cara penulisan tanda tesebut harus di dalam tanda petik dan kemudian digabungkan (concatenate) dengan tanggal teratas atau terbawah menggunakan bantuan operator ampersand (&). Untuk tanggal terbawah karena kita ingin menghitung jumlah tanggal diatasnya, maka digunakan tanda lebih dari atau sama dengan (">="), dan sebaliknya untuk tanggal teratas.

Contoh Rumus 2 : Menuliskan tanggal dengan referensi sel.

Sangat disarankan untuk menggunakan referensi sel sebagai tempat menuliskan tanggal terbawah dan teratas. Ini dimaksudkan supaya lebih mudah dimodifikasi jika di kemudian, kita perlu merubah tanggal terbawah dan tanggal teratasnya.

Misalnya tanggal terbawah diletakan pada sel E3, dan tanggal teratas diletakan di sel E4, maka rumus tadi dapat dimodifikasi menjadi seperti berikut ini:

=COUNTIFS(B2:B10,">="&E3,B2:B10,"<="&E4)

Berikut contoh penampakannya dalam spreadsheet.

Dari gambar di atas kita dapat menyimpulkan bahwa rumus ke-2 lebih ringkas karena tidak membutuhkan fungsi tambahan (fungsi DATE). Selain itu, parameter tanggal terbawah dan tanggal teratas akan lebih mudah dimodifikasi tanpa harus merubah rumus.

Sangat Penting: Baik contoh rumus ke-1 maupun ke-2, harus dipastikan cara penulisan tanggal sudah benar. Perhatikan kolom tanggal lahir (kolom B). data tanggal dalam tabel tersebut harus benar-benar data ber-type tanggal, bukan type text. Secara general (jika tidak ada edit alignment) data type tanggal akan nampak rata kanan. Jika tidak, maka kemungkinan data tersebut ber-type text dan akan error jikak diproses dengan rumus yang sudah dijelaskan di atas.

Demikian pembahasan singkat mengenai bagaimana menggunakan fungsi COUNTIFS untuk menghitung jumlah data diantara dua tanggal.

Semoga bermanfaat.

Terimakasih.

Artikel Terkait:

Menghitung Jumlah Data Unik

Menghitung Jumlah Text dengan Case Sensitive

Rumus VLOOKUP untuk Tanggal