Formula generik
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Ringkasan
Untuk menghilangkan beberapa kerumitan semula jadi teks (tanda baca jalur, menormalkan huruf besar, membuang ruang tambahan) anda boleh menggunakan formula berdasarkan fungsi SUBSTITUTE, dengan bantuan fungsi TRIM dan RENDAH.
Konteks
Mungkin ada kalanya anda perlu membuang beberapa kebolehubahan teks sebelum pemprosesan lain. Salah satu contohnya ialah ketika anda ingin menghitung kata-kata tertentu dalam rentetan teks yang lebih besar. Kerana Excel tidak memberikan sokongan untuk ungkapan biasa, anda tidak dapat membuat padanan yang tepat. Contohnya, jika anda ingin mengira berapa kali perkataan "rubah" muncul di dalam sel, anda akhirnya akan mengira "rubah". Anda boleh mencari "rubah" (dengan spasi) tetapi itu akan gagal dengan "rubah", atau "rubah". Satu penyelesaian adalah dengan mempermudah teks terlebih dahulu dengan formula di kolom penolong, kemudian jalankan versi pada versi yang dipermudahkan. Contoh di halaman ini menunjukkan satu cara untuk melakukan ini.
Penjelasan
Rumus yang ditunjukkan dalam contoh ini menggunakan rangkaian fungsi SUBSTITUTE bersarang untuk menghilangkan tanda kurung, tanda hubung, titik dua, titik koma, tanda seru, koma, dan noktah. Prosesnya berjalan dari dalam ke luar, dengan setiap SUBSTITUT menggantikan satu watak dengan satu ruang, kemudian diserahkan kepada SUBSTITUT berikutnya. SUBSTITUT paling dalam menghilangkan tanda kurung kiri, dan hasilnya diserahkan kepada SUBSTITUT berikutnya, yang menghilangkan tanda kurung kanan, dan seterusnya.
Dalam versi di bawah, jeda baris telah ditambahkan untuk dibaca, dan untuk mempermudah pengeditan penggantian. Excel tidak mementingkan jeda baris dalam formula, jadi anda boleh menggunakan formula sebagaimana adanya.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
Setelah semua penggantian selesai, hasilnya dijalankan melalui TRIM untuk menormalkan ruang, kemudian fungsi RENDAH untuk memaksa semua teks menjadi huruf kecil.
Catatan: Anda perlu menyesuaikan penggantian sebenar agar sesuai dengan data anda.
Menambah ruang utama dan belakang
Dalam beberapa kes, anda mungkin mahu menambahkan watak ruang pada permulaan dan akhir teks yang dibersihkan. Sebagai contoh, jika anda ingin menghitung perkataan dengan tepat, anda mungkin ingin mencari perkataan yang dikelilingi oleh spasi (iaitu mencari "rubah", "peta") untuk mengelakkan pencocokan palsu. Untuk menambahkan ruang depan dan belakang, cukup gabungkan ruang ("") ke awal dan akhir:
=" "&formula&" "
Di mana "formula" adalah formula yang lebih panjang di atas.
