Cara mengganti teks yang cocok dengan SUBSTITUTE di Excel
Gunakan SUBSTITUTE di Excel untuk mengganti kata atau simbol tertentu. Tanpa argumen keempat, semua kecocokan diganti. Jika diisi 2, hanya kecocokan kedua yang diganti. Fungsi membedakan huruf besar dan kecil, dan hasilnya terpisah dari sel sumber.
Menentukan teks sumber, teks yang dicari, dan pengganti
=SUBSTITUTE(text;old_text;new_text;[instance_num])
Argumen pertama adalah teks asli atau referensi sel. Argumen kedua adalah teks yang dicari dan argumen ketiga adalah penggantinya. Argumen keempat bersifat opsional dan menghitung kecocokan dari kiri ke kanan. Angka 2 berarti kecocokan kedua, di posisi mana pun.
Dokumentasi Microsoft tentang SUBSTITUTE
Membandingkan penggantian semua kecocokan dan kecocokan kedua
Masukkan AB-CD-cd-CD di A2 dan coba setiap rumus dalam sel kosong yang berbeda. CD dengan huruf besar muncul dua kali. cd dengan huruf kecil di tengah tidak cocok.
| Rumus | Hasil | Yang berubah |
|---|---|---|
=SUBSTITUTE(A2;"CD";"ZZ") |
AB-ZZ-cd-ZZ | Ganti kedua CD dengan huruf besar |
=SUBSTITUTE(A2;"CD";"ZZ";2) |
AB-CD-cd-ZZ | Ganti hanya CD kedua dengan huruf besar |
=SUBSTITUTE(A2;"CD";"ZZ";3) |
AB-CD-cd-CD | Kembalikan teks asli karena tidak ada kecocokan ketiga |
Menghapus tanda hubung dan spasi tanpa kehilangan nol di depan
Masukkan 00-27 04 sebagai teks di A6. Jika Excel mengubahnya, awali masukan dengan apostrof. Rumus pertama menghapus semua tanda hubung dan menghasilkan 0027 04. Rumus kedua juga menghapus spasi biasa dan menghasilkan 002704.
=SUBSTITUTE(A6;"-";"")
=SUBSTITUTE(SUBSTITUTE(A6;"-";"");" ";"")
Ketika fungsi disarangkan, hasil SUBSTITUTE bagian dalam diteruskan ke fungsi bagian luar. Dengan begitu beberapa jenis karakter bisa dihapus secara berurutan. Simpan kode sebagai teks jika nol di depan bermakna. Rumus ini tidak mengembalikan nol yang hilang saat data dimasukkan.
Panduan Microsoft untuk mempertahankan nol di depan
Memeriksa penggantian yang tidak terjadi
Periksa huruf besar dan kecil serta jenis tanda hubung dan spasi. Mencari spasi biasa tidak mencakup spasi tak terputus. Gunakan TRIM untuk menghapus spasi biasa di awal dan akhir dan menyisakan satu spasi di antara kata.
Gunakan REPLACE jika posisi selalu sama, meskipun isinya berbeda. Untuk mengganti dua karakter mulai dari posisi 4 pada setiap kode, tentukan posisi dan jumlah karakternya.




