Mengganti Teks dengan SUBSTITUTE

Data sering memerlukan pencarian dan penggantian di dalam rumus: menghapus tanda hubung dari nomor telepon, menukar sebuah kata, atau menghapus simbol yang terselip.

Fungsi SUBSTITUTE mencari bagian teks di dalam sel dan mengganti setiap kemunculannya dengan teks baru, tanpa mengubah sel asli.

Fungsi ini merupakan padanan berbasis rumus dari Cari & Ganti, tetapi diperbarui secara otomatis saat data Anda berubah.

SUBSTITUTE menerima hingga empat argumen:

• teks string atau sel asli
• teks_lama teks yang akan dicari
• teks_baru teks yang akan ditempatkan sebagai penggantinya
• nomor_instans (opsional) kemunculan yang akan diganti

Bentuknya adalah =SUBSTITUTE(text, old_text, new_text, [instance_num]). Jika argumen terakhir dihilangkan, semua kemunculan akan diganti.

Salah satu trik yang umum adalah mengganti sesuatu dengan teks kosong untuk menghapusnya. Caranya dengan menetapkan teks_baru sebagai string kosong "".

Jika A2 berisi 555-123-4567, rumus di bawah ini menghapus setiap tanda hubung dan menghasilkan 5551234567.

Pola ini berguna untuk membersihkan nomor telepon, pengenal, dan nilai lain yang mengandung tanda baca.

SUBSTITUTE juga bekerja pada seluruh kata, bukan hanya satu karakter.

Jika A2 berisi Laporan tahunan 2023, Anda dapat memperbarui tahunnya dengan mengganti kata tersebut:

Hasilnya adalah Laporan tahunan 2024. Setiap kemunculan yang cocok di dalam sel akan diubah.

Penting: SUBSTITUTE mencocokkan teks secara tepat, termasuk besar-kecil huruf.

Jika A2 berisi Kucing kucing CAT, maka =SUBSTITUTE(A2, "cat", "dog") hanya mengubah kucing yang ditulis dengan huruf kecil, sehingga menghasilkan Kucing anjing CAT.

Jika Anda memerlukan penggantian yang tidak peka terhadap besar-kecil huruf, normalkan besar-kecil huruf terlebih dahulu dengan LOWER atau UPPER, atau gunakan fungsi ekspresi reguler pada pelajaran berikutnya di Spreadsheet.

Argumen keempat yang bersifat opsional, nomor_instans, memungkinkan Anda memilih satu kemunculan tertentu, bukan semuanya.

Jika A2 berisi a-b-c-d dan Anda hanya ingin mengubah tanda hubung kedua, tetapkan nomor_instans ke 2.

Hasilnya adalah a-b/c-d: hanya tanda hubung kedua yang berubah menjadi garis miring.

Bayangkan jumlah yang diimpor dan disimpan sebagai teks: $1,250.00. Untuk mengubahnya menjadi angka yang dapat digunakan, Anda harus menghapus tanda dolar dan koma.

Sarangkan dua pemanggilan SUBSTITUTE, satu di dalam yang lain, untuk menghapus kedua karakter tersebut.

Pemanggilan bagian dalam menghapus koma; pemanggilan bagian luar menghapus tanda dolar, sehingga tersisa 1250.00.

Anda dapat menyarangkan sebanyak mungkin pemanggilan SUBSTITUTE sesuai kebutuhan, dengan setiap pemanggilan membersihkan karakter yang berbeda.

Untuk menormalkan kode berantakan yang berisi spasi, titik, dan garis miring menjadi tanda hubung yang rapi, rangkai tiga penggantian.

Setiap lapisan meneruskan hasilnya ke lapisan berikutnya, sehingga terbentuk string akhir yang sudah dibersihkan.

Jangan keliru membedakan SUBSTITUTE dengan fungsi REPLACE yang serupa.

• SUBSTITUTE mengganti teks berdasarkan pencocokan isinya (cari kata ini, lalu tukar).
• REPLACE mengganti teks berdasarkan posisinya (ganti 4 karakter yang dimulai dari posisi 5).

Gunakan SUBSTITUTE jika Anda mengetahui apa yang harus dicari, dan gunakan REPLACE jika Anda mengetahui di mana letaknya.

Satu manfaat tambahan yang cerdas: gabungkan SUBSTITUTE dengan LEN untuk menghitung berapa kali sesuatu muncul.

Kurangi panjang setelah menghapus suatu karakter dari panjang asli. Jika A2 berisi a,b,c,d, cara ini menghitung jumlah koma dan menghasilkan 3.

Trik ini sangat berguna untuk menghitung item dalam daftar yang dipisahkan pembatas.

Misalkan A2 berisi nomor berantakan dengan titik dan spasi seperti 555.123 4567. Anda menginginkan format bertanda hubung yang konsisten, yaitu 555-123-4567.

Pertama, hapus titik dan spasi, lalu Anda dapat menyisipkan kembali tanda hubung; untuk saat ini, cukup seragamkan semua pemisah menjadi tanda hubung.

Menyarangkan penggantian akan menyeragamkan pemisah apa pun yang tidak rapi.

Uji pemahaman Anda tentang SUBSTITUTE.

Anda telah mempelajari cara mencari dan mengganti teks di dalam rumus:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Gunakan "" sebagai teks_baru untuk menghapus karakter.
• Fungsi ini peka terhadap besar-kecil huruf; normalkan besar-kecil huruf terlebih dahulu jika diperlukan.
• Nomor_instans yang bersifat opsional memilih satu kemunculan tertentu.
• Sarangkan pemanggilan untuk membersihkan beberapa karakter; pasangkan dengan LEN untuk menghitung kemunculan.

Anda telah menyelesaikan bagian Menggabungkan dan Mengubah Teks. Kerja bagus dalam membangun data yang bersih dan konsisten!