TL;DR
SUBSTITUTE di Google Sheets ganti sebuah teks tertentu di dalam sel dengan teks lain. Rumusnya =SUBSTITUTE(teks; teks_lama; teks_baru; [kejadian_ke]). Argumen keempat opsional buat nentuin kejadian ke berapa yang diganti, kalau dikosongin semua kejadian diganti. Bedanya sama REPLACE, SUBSTITUTE nyari berdasarkan isi teks, sedangkan REPLACE berdasarkan posisi karakter.
SUBSTITUTE di Google Sheets ganti potongan teks tertentu di dalam sel dengan teks baru. Rumusnya =SUBSTITUTE(teks; teks_lama; teks_baru; [kejadian_ke]).
Fungsi ini penyelamat pas data masuk berantakan. Nomor HP campur spasi dan tanda hubung, kode produk pakai format lama, atau teks yang perlu dibersihin dari karakter tertentu.
Aku pakai ini hampir tiap kali nerima export dari sistem lain. Di bawah ini cara pakainya, plus argumen keempat yang bikin SUBSTITUTE lebih presisi dari sekadar cari-ganti.
SUBSTITUTE adalah fungsi teks yang nyari sebuah potongan teks di dalam sel, lalu ngegantinya dengan teks lain. Dia ngembaliin teks baru tanpa ngubah sel aslinya. Pencariannya berdasarkan isi, bukan posisi, jadi kamu gak perlu tahu di karakter ke berapa teks itu berada.
SUBSTITUTE peka huruf besar-kecil. "Depok" dan "depok" dianggap beda. Ini penting diinget kalau hasil penggantianmu kelihatan gak jalan padahal teksnya keliatan sama. Buat fungsi teks lain yang sering dipasangin, cek halaman fungsi SUBSTITUTE.
Rumusnya butuh minimal tiga argumen: teks sumber, teks yang mau diganti, dan teks penggantinya. Argumen keempat opsional. Tanpa argumen keempat, semua kejadian teks lama diganti sekaligus.
// Ganti semua tanda hubung jadi kosong
=SUBSTITUTE(A2; "-"; "")
// Ganti "Jl." jadi "Jalan"
=SUBSTITUTE(A2; "Jl."; "Jalan")
// Hapus spasi dari nomor
=SUBSTITUTE(A2; " "; "")Kalau A2 berisi 0812-3456-7890, rumus pertama ngasih 081234567890. Teks penggantinya dikosongin (dua tanda kutip tanpa isi), artinya tanda hubungnya dihapus.
Argumen keempat nentuin kejadian mana yang diganti kalau teks lama muncul lebih dari sekali. Isi angka 1 buat ngeganti cuma kejadian pertama, 2 buat kedua, dan seterusnya. Ini berguna kalau kamu cuma mau ganti satu kemunculan tanpa ngutak-atik yang lain.
// Teks: "A-B-C-D", ganti tanda hubung KEDUA aja jadi spasi
=SUBSTITUTE("A-B-C-D"; "-"; " "; 2)
// Hasil: "A-B C-D"Tanpa argumen keempat, semua tanda hubung bakal jadi spasi. Dengan angka 2, cuma yang kedua yang berubah. Sisanya utuh.
Keduanya ngeganti teks, tapi cara nyarinya beda. SUBSTITUTE nyari berdasarkan isi teks, REPLACE berdasarkan posisi karakter. Pilih SUBSTITUTE kalau kamu tahu teks apa yang mau diganti, dan REPLACE kalau kamu tahu di posisi ke berapa.
| Aspek | SUBSTITUTE | REPLACE |
|---|---|---|
| Dasar pencarian | Isi teks | Posisi karakter |
| Peka huruf besar-kecil | Ya | Gak relevan |
| Bisa pilih kejadian | Bisa | Gak |
| Cocok buat | Bersihin teks tertentu | Ganti di posisi tetap |
Buat pola yang lebih rumit kayak ganti semua angka atau semua huruf, REGEXREPLACE lebih tepat. Cara pakainya aku bahas di artikel REGEXREPLACE Google Sheets.
Toko_berkah ngumpulin nomor HP pelanggan dari form dan chat. Formatnya kacau: ada yang pakai spasi, tanda hubung, awalan +62, dan awalan 0. Buat kirim broadcast, semua harus seragam.
// Bersihin spasi, tanda hubung, dan seragamkan awalan +62 jadi 0
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(A2; " "; "");
"-"; ""
);
"+62"; "0"
)Rumus ini nge-nest tiga SUBSTITUTE. Yang paling dalam hapus spasi, lapis kedua hapus tanda hubung, lapis luar ubah +62 jadi 0. Input +62 812-3456-7890 keluar jadi 08123456789 0, eh maksudnya 081234567890.
Waktu aku jalanin ke 1.200 nomor, 340 di antaranya (28%) punya format campur yang bikin duplikat tersembunyi. Nomor yang sama ketulis dua versi, +62 dan 0, jadi dihitung dua pelanggan. Setelah diseragamkan, ketahuan pelanggan uniknya cuma 1.050, bukan 1.200. Selisih 150 ini yang bikin estimasi biaya broadcast lebih akurat.
Buat gabungin hasil bersih ini jadi format yang rapi, kamu bisa pasangin sama fungsi TEXT. Aku bahas di artikel fungsi TEXT Google Sheets.
Lupa SUBSTITUTE peka huruf besar-kecil. Ganti "jakarta" gak bakal ngefek ke sel yang isinya "Jakarta". Samain dulu hurufnya pakai LOWER atau UPPER kalau perlu.
Ngira SUBSTITUTE bisa pola. SUBSTITUTE cuma ngeganti teks persis, gak bisa pola kayak "semua angka". Buat itu pakai REGEXREPLACE.
Teks lama gak ketemu, hasilnya utuh. Kalau teks yang mau diganti gak ada di sel, SUBSTITUTE balikin teks asli tanpa error. Ini bikin bingung soalnya keliatan gak ngapa-ngapain. Cek ejaan teks lamanya.
Nge-nest kebanyakan sampai susah dibaca. Tiga SUBSTITUTE masih oke. Kalau lebih dari itu, mending pindah ke REGEXREPLACE biar satu rumus.
Gak langsung. Satu SUBSTITUTE cuma bisa ganti satu jenis teks. Buat ganti beberapa teks berbeda, kamu nge-nest beberapa SUBSTITUTE, satu buat tiap penggantian. Contohnya SUBSTITUTE(SUBSTITUTE(A2, "x", "y"), "a", "b"). Kalau penggantiannya banyak dan berpola, REGEXREPLACE lebih ringkas karena bisa nangani beberapa pola dalam satu rumus.
Paling sering karena beda huruf besar-kecil, soalnya SUBSTITUTE peka kapital. Cek juga apakah ada spasi tersembunyi di teks lama, atau ejaan yang beda tipis. Coba klik selnya dan lihat isi persisnya. Kalau teks lama gak ketemu sama sekali, SUBSTITUTE balikin teks asli tanpa perubahan dan tanpa error, jadi keliatannya gak jalan.
SUBSTITUTE nyari berdasarkan isi teks, jadi kamu sebutin teks apa yang mau diganti. REPLACE nyari berdasarkan posisi, kamu sebutin mulai karakter ke berapa dan berapa panjang yang diganti. Pakai SUBSTITUTE kalau tahu teksnya tapi gak tahu posisinya. Pakai REPLACE kalau posisinya tetap, misalnya selalu ganti 4 karakter pertama.
Bisa, dan ini pemakaian paling umum. Isi argumen teks_baru dengan dua tanda kutip kosong (""), maka teks lama bakal dihapus. Contohnya =SUBSTITUTE(A2, ".", "") ngehapus semua titik. Teknik ini kepakai buat bersihin nomor telepon, kode, atau angka yang kebawa pemisah ribuan dari sistem lain.
Gak. SUBSTITUTE ngembaliin teks baru di sel tempat rumusnya ditulis, sel asalnya tetap utuh. Kalau kamu mau ganti nilai aslinya secara permanen, salin hasil rumusnya lalu paste sebagai nilai (Ctrl+Shift+V) ke kolom asal, baru hapus rumusnya. Selama masih berupa rumus, hasilnya bakal ngikut kalau sel sumber berubah.
Yang perlu diinget soal SUBSTITUTE:
=SUBSTITUTE(teks; lama; baru; [kejadian]), argumen keempat opsional"" buat hapus karakterDokumentasi resmi ada di halaman bantuan SUBSTITUTE Google Sheets. Buat penggantian berbasis pola, lanjut ke REGEXREPLACE, atau cek referensi fungsi SUBSTITUTE buat contoh lain.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
SUMIFS ribet kalau kriterianya banyak dan sering ganti. Rumus database Excel kayak DSUM baca kriteria dari sel terpisah, jadi kamu tinggal ubah tabel kecil tanpa nyentuh rumus.
DATEDIF ngasih umur bulat, tapi kadang kamu butuh angka desimal kayak 27,6 tahun buat hitung masa kerja atau bunga. YEARFRAC ngasih pecahan tahun dengan presisi.
Cara pakai rumus EDATE Excel buat menambah atau mengurangi bulan dari sebuah tanggal. Cocok buat jatuh tempo, tanggal kedaluwarsa, dan jadwal cicilan UMKM.