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:

Cara Menghitung Jumlah Huruf dan Kata Pada Excel

• 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

Menghitung jumlah karakter atau kata dalam satu sel.

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

Cara Kerja Rumus Excel Untuk Menghitung Jumlah Huruf Dan Karakter Tertentu.

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",""))

Menghitung jumlah kata dengan membedakan huruf besar/kecil

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"

Menghitung jumlah kata Tanpa membedakan huruf besar/kecil

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")

Menghitung jumlah karakter atau kata dalam kumpulan sel.

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:

• Cara Memisahkan Kata Per Huruf
• Fungsi UPPER, LOWER dan PROPER
• Fungsi LEN untuk Menghitung Jumlah Huruf Pada Excel

Fungsi PROPER Untuk Text Belakang Tanda Petik

Dalam postingan sebelumnya sudah dibahas perihal cara menggunakan fungsi PROPER, UPPER dan LOWER untuk menyesuaikan besar kecilnya huruf dalam text. Khusus fungsi PROPER digunakan untuk menjadikan semua kata dalam text memiliki huruf awal kapital (uppercase), sedangkan huruf lainnya menjadi huruf kecil (lowercase). Permasalahan yang muncul seputar penggunaan fungsi PROPER, diantaranya adalah pada saat menggunakan fungsi tersebut pada text yang mengandung tanda petik.

Misalnya adalah text nama dengan tanda petik 1 (apostrope) seperti: nur'aini, syafi'i, ma'ruf dan sebagainya. Jika text tersebut diproses melalui rumus PROPER, maka huruf dibelakang tanda petik menjadi huruf besar. 

=PROPER("nur'aini")➜   hasilnya: "Nur'Aini"

=PROPER("syafi'i") ➜   hasilnya: "Syafi'I"

=PROPER("ma'ruf")  ➜   hasilnya: "Ma'Ruf"

Atau jika nama-nama tersebut disimpan dalam sel, maka rumus dan hasilnya dapat diilustrasikan sebagai berikut:

Padahal yang diharapkan hanya huruf pertama saja yang menjadi huruf kapital.

Bagaimana cara mengakali rumus PROPER untuk text bertanda kutip supaya menghasilkan text yang yang diharapkan? 

Dengan kata lain, bagaimana caranya agar huruf di belakang tanda kutip harus tetap sebagai huruf kecil?

Hal ini bisa menggunakan bantuan fungsi SUBSTITUSI dan text yang unik dan jarang digunakan dalam kata atau kalimat yang normal.

Misalnya buat saja rumus seperti ini untuk mem-PROPER text "nur'aini" menjadi "Nur'aini" , bukan menjadi "Nur'Aini".

=SUBSTITUTE(PROPER(SUBSTITUTE("nura'aini","'","vwxyz")),"vwxyz","'")

Penjelasan cara kerja rumus

• Rumus SUBSTITUTE("nura'aini","'","vwxyz") , pertama-tama akan merubah tanda petik 1 dalam text "nur'aini" menjadi text "vwxyz", sehingga menghasilkan text "nurvwxyzaini"

• Kemudian fungsi PROPER, akan mengkonvert text "nurvwxyzaini" menjadi "Nurvwxyzaini"

• Terakhir fungsi SUBSTITUTE berguna untuk mengembalikan "text vwxyz" dalam text "Nurvwxyzaini" menjadi tanda petik kembali sehingga hasil akhirnya adalah text "Nur'aini".

• Persis hasilnya sesuai yang diharapkan. 

• Jika diterapkan dengan referensi sel, maka dapat diilustrasikan seperti gambar dibawah ini:

Perhatikan text "vwxyz" dalam contoh rumus di atas.  Text tersebut bisa diganti dengan text lainnya yang menurut anda tidak mungkin ada  dalam nama orang, nama benda atau dalam kalimat dan kata yang normal digunakan.

Demikian pembahasan singkat bagaimana menggunakan fungsi PROPER untuk mengolah text yang bertanda petik satu (apostrope) supaya dapat mempertahankan huruf dibelakang tanda petik  tetap sebagi huruf kecil.

Semoga bermanfaat.

Belajar Excel.. Excellent!

Baja juga artikel tutorial belajar excel lainnya:

• Cara Menggunakan Fungsi LOWER, UPPER, dan PROPER
• Cara Memisahkan Kata Per Huruf
• Cara Menghilangkan Spasi Berlebih Dari Text
• List Artikel Belajar Excel

Cara Memisahkan Kata Per Huruf

Pembaca je-xcel yang budiman, barangkali kita pernah mengisi form-form berupa kotak isian text nama, alamat, nomor telepon dan sebagainya. Masing-masing isian dipisahkan per huruf, bukan per kata. Biasanya hal ini dijumpai dalam form-form tertentu semisal form registrasi rekening di bank, ataupun form-form lainnya.

Tahukan teman?... ternyata kita juga dapat mengerjakan hal itu di excel, tapi bukan dengan mengetik satu huruf per satu kotak/sel. Namun kita bisa merumuskannnya sehingga cukup hanya mengetikan kata-kata atau text lengkap (misal nama atau alamat) dalam satu sel saja. Tugas selanjutnya serahkan saja kepada rumus excel yang akan memisahkan text atau kata-kata tersebut secara otomatis menjadi per  huruf dalam kotak atau sel yang berbeda.

Sebelum membaca lebih lanjut, boleh dong dicek dulu videonya ..😊😊

Bagaimana cara memisahkan kata menjadi per huruf?

Cara memisahkan text menjadi per huruf cukup mudah, yaitu dengan memanfaatkan fungsi MID. Fungsi ini bekerja dengan cara mengambil satu atau beberapa huruf atau karakter sesuai nomor urut karakter tersebut dalam text.

Fungsi MID dapat dituliskan sebagai berikut:

MID(text,start_num,num_char)

Dimana:

• text =  text atau string utama yang akan kita ambil bagian karakter nya
• start_num =  nomor urut karakter pertama dari string yang akan diambil dari text utama
• num_char =  banyaknya huruf atau karakter yang akan diambil dari text utama, dimulai dari start_num.

Contoh Rumus:

=MID("BANDUNG",4,2)

Rumus tersebut akan mengahasilkan text  "DU",  yaitu 2 karakter dimulai dari karakter ke 4

Mari kita coba selanjutnya... Bagaimana jika kita hanya mengambil satu huruf saja per nomor urut karakter dalam text.

=MID("BANDUNG",1,1) menghasilkan  huruf  "B"   (karakter ke 1)
=MID("BANDUNG",2,1) menghasilkan  huruf  "A"   (karakter ke-2)
=MID("BANDUNG",3,1) menghasilkan  huruf  "N"   (karakter ke-3)
=MID("BANDUNG",4,1) menghasilkan  huruf  "D"   (karakter ke-4)
=MID("BANDUNG",5,1) menghasilkan  huruf  "U"   (karakter ke-5)
=MID("BANDUNG",6,1) menghasilkan  huruf  "N"   (karakter ke-6)
=MID("BANDUNG",7,1) menghasilkan  huruf  "G"   (karakter ke-7)

Nah, Konsep tersebut kemudian dapat kita manfaatkan untuk memisahkan kata-kata menjadi huruf atau karakter-karakter tunggal.

Contoh Rumus Untuk Memisahkan Kata Per Huruf

Ada beberapa metode rumus yang dapat digunakan untuk memisahkan text kata-kata menjadi per huruf atau per karakter. Namun entah apapun metode yang digunakan, cara kerjanya hampir sama yaitu mendapatkan angka nomor urut karakter, dan menjadikannya sebagai argumen start_num dalam fungsi MID.

Contoh 1: Memisahkan Kata Menjadi Per huruf Menggunakan Fungsi MID dan Baris  Bantu

Cara Kerja Rumus:

Perhatikan contoh rumus pada gambar diatas.

• Baris bantu pada range (C2:V2) digunakan berisi nomor urut 1 s.d 20 digunakan untuk mendefinisikan argumen start_num dalam fungsi MID. Dengan kata lain berguna untuk mendefinisikan nomor urut huruf dalam text yang akan dimunculkan dalam kolom tersebut.
• Perhatikan rumus pada sel C4 yaitu =MID($B4,C$2,1)  (sesuai tanda panah)
• $B4 adalah text utama, berupa data nama yang akan kita ambil per hurufnya. Tanda dolar ($) dimaksudkan supaya kalau rumus tersebut dicopy ke samping, maka referensi kolomnya akan tetap mengikat ke kolom B yang berisi list Nama
• C$2 berperan sebagai argumen star_num yaitu nomor urut karakter dalam text yang akan diambil.
• Angka 1, menunjukan hanya satu hurus saja yang akan diambil dimulai dari nomur urut (star_num)
• Copy rumus tersebut ke semua kotak di sebelahnya.
• Anda dapat menambahkan baris baru untuk  item list nama yang lainnya, dan copy rumus tadi.
• Selesai...

Contoh 2: Memisahkan kata per huruf menggunakan rumus kombinasi fungsi MID dan COLUMNS

Cara Kerja Rumus:

• Perhatikan rumus pada sel C4 yaitu =MID($B4,COLUMNS($B:B),1)  (sesuai tanda panah)
• $B4 mengikat pada text nama yang akan dipisahkan per karakter, tanda dolar hanya sebelum kolom menandakan referensi sel semi absolute supaya jika rumus dicopy ke sel lain maka referensi kolom tidak berubah relative, tetapi baris bisa berubah.
• COLUMNS($B:B) berguna untuk mendapatkan jumlah kolom pada range $B:B,  rumus tersebut akan menghasilkan angka 1 karena range B:B hanya memiliki 1 kolom. Tanda $ hanya pada salah satu label huruf B dimaksudkan supaya jika dicopy ke samping maka kolom pertama akan terkunci, tetapi kolom akhir tidak terkunci, sehingga jika rumus dicopy ke sel sebelahnya, alamat range akan berubah menjadi $B:C , $B:D, $B:E dan seterusnya dan memberikan angka 2, 3, 4 dan seterusnya
• Angka yang dihasilkan fungsi COLUMNS digunakan oleh fungsi MID sebagai argumen start_num untuk mendapatkan satu huruf atau satu karakter dari text utama.
• Silahkan rumus di sel C4 di copy paste ke sel lainnya.
• Kelemahan dari metode ini adalah hasil akan menjadi tidak relevan jika ada insert column.

Contoh 3: Memisahkan Kata per Huruf Menggunakan Rumus Array CSE

Cara Kerja Rumus:

• Perhatikan rumus pada range C4:V4 (sesuai tanda panah)
• Rumus tersebut dibuat dengan cara menyeleksi range C4:V4, kemudian mengetikan rumus =MID(B4,COLUMN(1:1),1), dan setelah itu tekan CTR+SHIFT+ENTER.
• Perhatikan kembali komponen rumus {=MID(B4,COLUMN(1:1),1)}
• Tanda kurung kurawal {} menandakan rumus array dimana tanda tersebut muncul secara otomatis ketika kita menekan CTR+SHIFT+ENTER setelah mengetik/mengedit rumus.  
• B4 berperan sebagai referensi berisi text utama yang akan diambil per hurufnya
• COLUMN(1:1) merupakan kunci untuk mendapatkan nomor urut karakter, dimana jika fungsi tersebut dimasukan dalam fungsi array maka akan menghasilkan angka 1 s.d 16384 di excel 2007 s.d 2016, sedangkan di excel 2003 akan menghasilkan angka 1 s.d 256 sesuai jumlah kolom yang diakomodir oleh masing-masing versi excel tersebut.
• Sebenarnya tidak masalah apakah kita menggunakan (COLUMN(1:1), COLUMN(1:2), COLUMN(3:10), dan sebagainya. Hasilnya akan sama dalam kasus penyelesaian masalah yang menjadi topik posting blog saat yang sedang anda baca ini.
• Elemen array yang dihasilkan rumus COLUMN(1:1) kemudian dijadikan sebagai parameter start_num dalam rumus MID.  Dan tentunya hanya beberapa elemen array saja yang digunakan sesuai jumlah karakter dalam kata atau text nama yang diproses.
• Untuk mengcopy rumus array tidak bisa satu sel per sel, tetapi harus dengan cara menyeleksi semua sel dalam lingkup array, dalam contoh yaitu range  C4:V4, kemudian paste pada sel C5, C6 dan seterusnya sesuai baris item list nama yang tersedia.

Ringkasan.

Untuk memisahkan kata-kata atau text menjadi per huruf dapat dilakukan menggunakan fungsi MID. Ada beberapa alternative metode yang dapat digunakan, diantaranya: Memisahkan text menjadi perhuruf menggunakan fungsi MID + baris bantu, menggunakan kombinasi fungsi MID dan COLUMNS, serta menggunakan rumus Array fungsi MID dan COLUMN.

Sekian, semoga bermanfaat.

Belajar Excel.! Excellent!

Artikel terkait:

• Cara Meggabungkan Kata di Excel
• Cara Menghilangkan Spasi Berlebih
• Belajar Rumus Array Untuk Pemula
• Fungsi Pengolah Text di Excel

Cara Menghilangkan Spasi Berlebih Pada Text

Bagaimana cara menghilangkan kelebihan spasi pada bagian awal, bagian tengah dan bagian akhir text? Dalam beberapa kasus, mungkin kita bisa menghapus spasi yang tidak diperlukan hanya cukup dengan menggunakan Fungsi TRIM.  Akan tetapi dalam prakeknya tidak selamanya Rumus Excel TRIM dapat memberikan hasil yang diharapkan. Kenapa? Silahkan dibaca selanjutnya.

Spasi berlebih pada data excel biasanya sering dijumpai terutama jika kita sering bekerja dengan data yang di export/import dari aplikasi lain. Meskipun demikian tidak menutup kemungkinan , baik dengan sengaja ataupun tidak, kita menambahkan sendiri spasi pada saat mengetik di excel.

Menghilangkan Spasi Berlebih Menggunakan Fungsi TRIM

Cara yang paling umum digunakan untuk menghapus spasi berlebih yang tidak diperlukan adalah menggunakan fungsi TRIM. Syntax fungsi ini sangat sederhana, dengan satu argumen text.

=TRIM(text)

Argumen text, bisa terdiri atas angka, huruf maupun symbol-symbol.

Contoh:

Anggaplan kita memiliki text  " Kota     Bandung  "

Text tersebut memiliki spasi berlebih yang tidak diperlukan pada bagian awal, bagian tengah antara dua kata, maupun bagian akhir.

• Bagian awal ada satu spasi, dan sebenarnya tidak diperlukan
• Bagian Tengah ada lima spasi, dan sebenarnya cukup  satu saja yang diperlukan
• Bagian akhir ada dua spasi, dan sebenarnya semuanya tidak diperlukan.

Jadi totalnya ada 7 spasi yang tidak diperlukan. Nah kelebihan spasi ini bisa dihilangkan menggunakan rumus excel TRIM.

=TRIM(" Kota     Bandung  ")

Atau jika text disimpan di sel B2

=TRIM(B2)

Hasil = "Kota Bandung"

Berhasil! Spasi berlebih pun dapat dihapus, baik pada bagian awal, tengah maupun akhir.

Catatan: 

Pada screenshot diatas, saya menggunakan tanda kurung [] untuk memvisualkan batas spasi pada bagian awal dan akhir text, sehingga nampak secara visual perubahannya sebelum dan sesudah TRIM. Sedangkan rumus LEN digunakan untuk mengetes jumlah karakter sebelum dan sesudah TRIM

Sudah Menggunakan Rumus TRIM, Tetapi Spasi Berlebih Tetap Tidak Berkurang.

Nah untuk kasus ini, barangkali anda pernah mengalaminya. Apa penyebabnya? Kenapa rumus TRIM tidak bekerja.

Contohnya:  silahkan dicopy rumus berikut ke dalam lembar kerja excel. Jangan mengetik rumus sendiri ya. Cukup copykan saja rumus di bawah ini.

=TRIM(" Kota     Bandung  ")

Perhatikan hasilnya.. 

Atau lebih jelasnya perhatikan contoh dalam screenshot berikut:

Rumus TRIM tidak bekerja, sehingga spasi  berlebih pun tidak berkurang, kenapa?

Ini jawabannya.

Silahkan ganti fungsi TRIM menjadi CODE, atau copy rumus berikut ke sel excel dan lihat hasilnya.

=CODE(" Kota     Bandung  ")

Hasil = 160

Sekarang ambil contoh rumus TRIM yang berhasil sebelumnya dan ganti TRIM menjadi CODE, atau copykan saja rumus berikut ke excel

=CODE(" Kota     Bandung  ")

Hasil = 32

Rumus secara visual nampak sama, namun memberikan hasil yang berbeda. Lalu penyebabnya apa?

Ternyata ada beberapa jenis spasi, diantaranya adalah spasi dengan code 32 atau CHAR(32) , dan spasi dengan code 160 atau CHAR(160) atau nonbreaking space.

Dan ternyata lagi, fungsi TRIM hanya dapat bekerja pada CHAR(32), tetapi tidak pada CHAR(160)

Solusinya bagaimana untuk menghilangkan spasi CHAR(160) yang tidak diperlukan.

Untuk kasus ini kita dapat menggunakan bantuan fungsi SUBSTITUTE untuk merubah CHAR(160) menjadi CHAR(32) sebelum dilakukan TRIM. Sehingga rumusnya menjadi:

=TRIM(SUBSTITUTE(" Kota     Bandung  ",CHAR(160),CHAR(32)))

atau 

=TRIM(SUBSTITUTE(" Kota     Bandung  ",CHAR(160)," "))

Jika contoh text disimpan di sel B2, maka rumusnya menjadi:

=TRIM(SUBSTITUTE(B2,CHAR(160),CHAR(32)))

atau

=TRIM(SUBSTITUTE(B2,CHAR(160)," "))

Untuk lebih jelasnya, perhatikan screenshot berikut:

Ringkasan:

Umumnya kita bisa menghilangkan spasi berlebih dengan cukup menggunakan fungsi TRIM. Dan untk kasus tertentu jika spasi berlebih berupa nonbreaking space atau CHAR(160) maka spasi berlebih tersebut harus dirubah terlebih dahulu menjadi spasi biasa atau CHAR(32) sebelum proses TRIM.

Semoga bermanfaat.

Belajar excel !  Excellent !

Artikel terkait:

• Cara Menggabungkan Kata Di Excel
• Cara Menghitung Jumlah Huruf di Excel
• Cara Mendapatkan Nama Hari dan Bulan Dalam Berbagai Bahasa