Formula umum
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Ringkasan
Untuk menghapus beberapa kompleksitas alami teks (menghilangkan tanda baca, menormalkan huruf besar-kecil, menghapus spasi ekstra) Anda dapat menggunakan rumus berdasarkan fungsi SUBSTITUTE, dengan bantuan dari fungsi TRIM dan LOWER.
Konteks
Mungkin ada kalanya Anda perlu menghapus beberapa variabilitas teks sebelum pemrosesan lainnya. Salah satu contohnya adalah ketika Anda ingin menghitung kata tertentu di dalam string teks yang lebih besar. Karena Excel tidak menyediakan dukungan untuk ekspresi reguler, Anda tidak bisa membuat pencocokan tepat. Misalnya, jika Anda ingin menghitung berapa kali kata "rubah" muncul dalam sel, Anda akan menghitung "rubah". Anda dapat mencari "rubah" (dengan spasi) tetapi akan gagal dengan "rubah", atau "rubah." Salah satu solusinya adalah menyederhanakan teks terlebih dahulu dengan rumus di kolom pembantu, lalu menjalankan penghitungan pada versi yang disederhanakan. Contoh di halaman ini menunjukkan satu cara untuk melakukan ini.
Penjelasan
Rumus yang diperlihatkan dalam contoh ini menggunakan serangkaian fungsi SUBSTITUTE bertumpuk untuk menghapus tanda kurung, tanda hubung, titik dua, titik koma, tanda seru, koma, dan titik. Prosesnya berjalan dari dalam ke luar, dengan setiap SUBSTITUTE mengganti satu karakter dengan satu spasi, kemudian berpindah ke SUBSTITUTE berikutnya. PENGGANTI paling dalam menghapus tanda kurung kiri, dan hasilnya diserahkan ke PENGGANTI berikutnya, yang menghapus tanda kurung kanan, dan seterusnya.
Pada versi di bawah ini, jeda baris telah ditambahkan agar mudah dibaca, dan untuk memudahkan pengeditan pengganti. Excel tidak peduli dengan jeda baris dalam rumus, jadi Anda bisa menggunakan rumus sebagaimana adanya.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
Setelah semua substitusi selesai, hasilnya dijalankan melalui TRIM untuk menormalkan spasi, kemudian fungsi LOWER untuk memaksa semua teks menjadi huruf kecil.
Catatan: Anda harus menyesuaikan penggantian sebenarnya agar sesuai dengan data Anda.
Menambahkan spasi di depan dan di belakang
Dalam beberapa kasus, Anda mungkin ingin menambahkan karakter spasi di awal dan akhir teks yang dibersihkan. Misalnya, jika Anda ingin menghitung kata dengan tepat, Anda mungkin ingin mencari kata yang dikelilingi oleh spasi (yaitu, telusuri "rubah", "peta") untuk menghindari kecocokan palsu. Untuk menambahkan spasi di depan dan di belakangnya, cukup gabungkan spasi ("") ke awal dan akhir:
=" "&formula&" "
Dimana "rumus" adalah rumus yang lebih panjang di atas.
