Menghitung Jumlah Karakter Tertentu Di Excel

Rumus Formula Excel Kumpulan

Cara Menghitung Jumlah Huruf dan Kata

Masih perihal rumus yang bekerja pada data text. Kali ini kita akan membahas bagaimana cara menghitung jumlah huruf , karakter atau kata tertentu dalam sebuah text atau string. Menghitung jumlah bagian text tertentu bisa dilakukan dengan satu formula saja, baik itu menghitung text dalam satu sel saja , maupun menghitung text yang terdapat dalam range atau kumpulan sel .

Secara garis besar, struktur pembahasan belajar excel kali ini mencakup:

Menghitung jumlah karakter atau kata dalam satu sel.

Menghitung huruf atau karakter tunggal

Dengan membedakan huruf besar/kecil
Tanpa Membedakan huruf besar/kecil

Menghitung jumlah kata

Dengan membedakan huruf besar/kecil
Tanpa membedakan huruf besar/kecil

Menghitung jumlah karakter atau kata dalam kumpulan sel.

Menghitung huruf atau karakter tunggal

Dengan membedakan huruf besar/kecil
Tanpa Membedakan huruf besar/kecil

Menghitung jumlah kata

Dengan membedakan huruf besar/kecil
Tanpa membedakan huruf besar/kecil

Anggaplah kita memiliki text yang akan dihitung jumlah huruf, karakter atau kata dalam text tersebut.

Text :	Saya anak yang baik. Anak-anak lain suka saya
Lokasi :	Sel B2

Maka rumus yang bisa digunakan dapat dilihat dalam tabel contoh di bawah ini.

No	Menghitung	Hasil	Formula
1	Semua Karakter	45	=LEN(B2)
2	Huruf "a"	13	=LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))
3	Huruf "A"	1	=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))
4	Huruf "a" dan "A"	14	=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))
5	Kata "anak"	2	=(LEN(B2)-LEN(SUBSTITUTE(B2,"anak","")))/LEN("anak")
6	Kata "Anak"	1	=(LEN(B2)-LEN(SUBSTITUTE(B2,"Anak","")))/LEN("Anak")
7	Kata "anak" dan "Anak"	3	=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"anak","")))/LEN("anak")

Bisa diperhatikan kembali pada contoh diatas, bahwa secara umum fungsi yang dibutuhkan untuk keperluan perhitungan karakter atau kata adalah LEN dan SUBSTITUTE serta UPPER atau LOWER

Fungsi LEN berguna untuk menghitung jumlah karakter dalam text.
Fungsi SUBSTITUTE berguna untuk mengganti bagian text atau karakter tertentu dengan text atau karakter lainnya. Fungsi ini bersifat Case Sensitive, sehingga menganggap beda antara huruf kecil dengan kapital (contoh : huruf “a” dan huruf “A”).
Fungsi UPPER berguna untuk merubah semua huruf dalam text menjadi huruf kapital semuanya
Fungsi LOWER berguna untuk merubah semua huruf dalam text menjadi huruf kecil semuanya

Menghitung karakter atau huruf tunggal dengan membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "a" dalam text yang berada dalam sel B2

=LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))

Fungsi SUBSTITUTE akan menukar karakter atau huruf yang dihitung dengan text kosong
Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
Selisih itulah yang merupakan jumlah karakter yang dicari.

Menghitung karakter atau huruf tunggal tanpa membedakan huruf besar/kecil

Contoh: Menghitung jumlah huruf "a" dan "A" dalam text yang berada dalam sel B2

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))

Fungsi UPPER akan merubah semua huruf dalam text menjadi huruf besar semua
Fungsi SUBSTITUTE akan menukar karakter atau huruf yang dihitung (A) dengan text kosong
Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
Selisih itulah yang merupakan jumlah karakter yang dicari.
Dengan konsep yang serupa, kita juga bisa mengganti fungsi UPPER menjadi LOWER

=LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"a",""))

Contoh: Menghitung jumlah huruf "Anak" dalam text yang berada dalam sel B2

=(LEN(B2)-LEN(SUBSTITUTE(B2,"Anak","")))/LEN("Anak")

Fungsi SUBSTITUTE akan menukar kata "Anak" dengan text kosong
Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
Selisihnya kemudian dibagi dengan banyaknya karakter kata "Anak"
Hasil pembagian itulah yang merupakan hasil akhirnya yaitu jumlah kata "Anak"

Contoh: Menghitung jumlah huruf "Anak" dan "Anak" dalam text yang berada dalam sel B2

=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"anak","")))/LEN("anak")

Fungsi LOWER akan merubah semua huruf dalam text menjadi huruf kecil semua
Fungsi SUBSTITUTE akan menukar kata "anak" dengan text kosong
Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
Selisihnya kemudian dibagi dengan banyaknya karakter kata "anak"
Hasil pembagian itulah yang merupakan hasil akhirnya yaitu jumlah kata "Anak" dan "anak"
Dengan konsep yang serupa, kita juga bisa mengganti fungsi LOWER menjadi UPPER

=(LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"ANAK","")))/LEN("ANAK")

Anggaplah text yang akan kita hitung adalah seperti berikut:

Text :	Nama saya Saleh
	Saya anak yang baik
	Anak-anak lain suka saya
	Apalagi anak komplek
Lokasi :	Sel B2:B5

Maka rumus untuk menghitung kehadiran karakter atau kata dalam kumpulan sel B2:B5 dapat diformulasikan seperti contoh dalam tabel di bawah ini:

No	Menghitung	Jumlah	Formula
1	Semua Karakter	78	=SUMPRODUCT(LEN(B2:B5))
2	Huruf "a"	22	=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"a","")))
3	Huruf "A"	2	=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"A","")))
4	Huruf "a" dan "A"	24	=SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(UPPER(B2:B5),"A","")))
5	Kata "anak"	3	=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"anak","")))/LEN("anak"))
6	Kata "Anak"	1	=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"Anak","")))/LEN("Anak"))
7	Kata "anak" dan "Anak"	4	=SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(LOWER(B2:B5),"anak","")))/LEN("anak"))

Dapat kita perhatikan dari contoh diatas, bahwa perbedaan mendasar dari rumus yang digunakan untuk menghitung banyaknya huruf atau kata dalam kumpulan sel dibandingkan dalam satu sel saja adalah adanya penggunaan fungsi SUMPRODUCT.

Cara kerja rumus untuk menghitung huruf dan kata pada kumpulan sel sebenarnya hampir sama saja dengan cara kerja rumus untuk menghitung huruf dan kata pada satu sel.

Yang membedakannya adalah karena bekerja pada array atau kumpulan sel maka perlu ditambahkan fungsi untuk menjumlahkan hasil dari masing-masing sel. Tambahan fungsi yang digunakan di sini adalah SUMPRODUCT.

Sebagai alternative lainnya, kita juga bisa menggunakakan rumus array CSE. Cukup ganti SUMPRODUCT menjadi SUM, dan jangan lupa untuk tekan CTR+SHIFT+ENTER setelah mengetikan rumusnya.

No	Menghitung	Jumlah	Formula
1	Semua Karakter	78	{=SUM(LEN(B2:B5))}
2	Huruf "a"	22	{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"a","")))}
3	Huruf "A"	2	{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"A","")))}
4	Huruf "a" dan "A"	24	{=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(UPPER(B2:B5),"A","")))}
5	Kata "anak"	3	{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"anak","")))/LEN("anak"))}
6	Kata "Anak"	1	{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"Anak","")))/LEN("Anak"))}
7	Kata "anak" dan "Anak"	4	{=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(LOWER(B2:B5),"anak","")))/LEN("anak"))}

Ringkasan:

Cara menghitung banyaknya huruf atau kata tertentu pada data excel dapat dilakukan menggunakan rumus kombinasi fungsi LEN,SUBSTITUTE,UPPER/LOWER serta operasi matematika pengurangan dan pembagian. Jika text yang dihitung terdapat pada kumpulan sel, maka cukup tambahkan fungsi SUMPRODUCT pada rumus kombinasi tadi. Sebagai alternative, fungsi SUMPRODUCT juga bisa diganti fungsi SUM dalam bentuk rumus array.

Demikian semoga bermanfaat.

Salam.. 😉

Baca juga tutorial belajar excel lainnya: