TL;DR
SUBSTITUTE mengganti teks berdasarkan isi yang kamu sebut, misalnya semua tanda hubung jadi kosong. REPLACE mengganti karakter berdasarkan posisi dan jumlah, misalnya karakter ke-5 sampai ke-8. Pakai SUBSTITUTE kalau kamu tau teks apa yang mau diganti, pakai REPLACE kalau kamu cuma tau posisinya.
SUBSTITUTE ganti teks berdasarkan isinya. REPLACE ganti berdasarkan posisinya.
Itu satu-satunya beda yang perlu kamu inget. Sisanya tinggal ngikut.
=SUBSTITUTE(A2;"-";"") → buang semua tanda hubung
=REPLACE(A2;5;4;"xxxx") → ganti 4 karakter mulai posisi ke-5Kalau kamu tau teks apa yang mau dibuang, pakai SUBSTITUTE. Kalau kamu cuma tau posisinya, pakai REPLACE.
SUBSTITUTE nyari sebuah teks di dalam sel, terus nukar semua kemunculannya dengan teks lain.
=SUBSTITUTE(teks; teks_lama; teks_baru; [kemunculan_ke])| Argumen | Isi | Wajib? |
|---|---|---|
teks | Sel yang mau dibersihin | Ya |
teks_lama | Yang mau diganti | Ya |
teks_baru | Penggantinya. Kosongin buat hapus | Ya |
kemunculan_ke | Ganti yang ke berapa aja | Nggak |
Argumen keempat itu yang sering dilupain, padahal berguna. Tanpa dia, SUBSTITUTE ganti semua kemunculan.
=SUBSTITUTE("PRD-2026-001";"-";"/") → PRD/2026/001
=SUBSTITUTE("PRD-2026-001";"-";"/";2) → PRD-2026/001Angka 2 artinya: cuma tanda hubung kedua yang diganti.
REPLACE nggak peduli isi teksnya. Dia cuma butuh tau mulai dari mana dan berapa karakter.
=REPLACE(teks_lama; posisi_awal; jumlah_karakter; teks_baru)Contoh paling sering: sensor nomor kartu.
=REPLACE("1234567812345678";5;8;"xxxxxxxx")
→ 1234xxxxxxxx5678Mulai dari karakter ke-5, ambil 8 karakter, ganti dengan x. Empat angka depan dan belakang tetap kelihatan.
Kamu nggak perlu tau isi nomornya. Cuma perlu tau posisinya.
| Kasus | Pakai | Kenapa |
|---|---|---|
| Buang tanda hubung dari nomor HP | SUBSTITUTE | Kamu tau yang dibuang itu - |
| Sensor 8 digit tengah kartu | REPLACE | Isinya beda tiap baris, posisinya sama |
| Ganti "Jl" jadi "Jalan" | SUBSTITUTE | Berdasarkan teks |
| Ubah 3 huruf awal kode cabang | REPLACE | Berdasarkan posisi |
| Hapus titik ribuan dari angka teks | SUBSTITUTE | Berdasarkan teks |
Dari pengalaman aku, SUBSTITUTE kepakai 9 dari 10 kali. REPLACE cuma menang di kasus posisi tetap, sensor data, atau kode yang formatnya fixed.
Nomor HP dari form online biasanya campur aduk: ada tanda hubung, spasi, titik, kurung. Tumpuk SUBSTITUTE-nya.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2;"-";"");" ";"");".";"")Bacanya dari dalam ke luar. SUBSTITUTE paling dalam jalan duluan, hasilnya jadi input buat yang di luarnya.
Masih ada satu masalah: awalan +62 dan 0 yang campur. Tambahin satu lapis lagi:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A2;"-";"");" ";"");".";"");"+62";"0")Panjang, tapi jalan. Kalau lapisannya udah lewat 5, mending pecah jadi beberapa kolom bantu biar masih kebaca besok pagi.
Dataset toko_berkah punya kolom alamat dengan 1.240 baris. Yang nulis alamat itu pelanggan sendiri lewat form, jadi singkatannya beragam: "Jl", "jl.", "JL", "Jalan".
Waktu tim marketing mau bikin segmentasi per jalan, hasilnya kacau. Satu jalan yang sama muncul empat versi.
=SUBSTITUTE(SUBSTITUTE(PROPER(TRIM(A2));"Jl.";"Jalan");"Jl ";"Jalan ")Setelah diseragamkan, jumlah nama jalan unik turun dari 612 jadi 478. 134 alamat yang tadinya dianggap beda ternyata jalan yang sama.
Rumusnya pakai TRIM dan PROPER dulu supaya spasi dan hurufnya seragam. Detail dua fungsi itu ada di rumus TRIM Excel dan rumus UPPER, LOWER, PROPER Excel.
Ini penggunaan SUBSTITUTE yang jarang orang tau. Mau hitung ada berapa koma di sebuah sel?
=LEN(A2) - LEN(SUBSTITUTE(A2;",";""))Logikanya: hitung panjang aslinya, hitung panjang setelah semua koma dibuang, selisihnya = jumlah koma.
Berguna buat validasi. Misalnya kolom yang harusnya berisi 3 tag dipisah koma. Kalau selisihnya bukan 2, ada yang salah input. LEN yang ngerjain hitungannya.
=REPLACE(A2;"-";"") dan dapat #VALUE!. REPLACE minta angka posisi, bukan teks.=SUBSTITUTE(A2;"jl";"Jalan") nggak kena "JL" atau "Jl". Seragamkan hurufnya dulu pakai LOWER atau PROPER.Referensi resmi kedua fungsi ini ada di dokumentasi SUBSTITUTE dari Microsoft.
Kalau kamu pindah ke database, ada sedikit kebingungan nama. Fungsi REPLACE di SQL sebenernya berperilaku kayak SUBSTITUTE di Excel. Ganti berdasarkan isi, bukan posisi.
-- SQL: ini setara SUBSTITUTE di Excel
SELECT REPLACE(no_hp, '-', '') FROM pelanggan;Nama sama, perilaku beda. Aku bahas lengkap di REPLACE SQL.
SUBSTITUTE ganti berdasarkan isi teks yang kamu sebut. REPLACE ganti berdasarkan posisi dan jumlah karakter. Buat pembersihan data sehari-hari, SUBSTITUTE jauh lebih sering kepakai.
Isi argumen keempat: =SUBSTITUTE(A2;"-";"/";2). Angka 2 artinya cuma tanda hubung kedua yang diganti. Kalau dikosongin, semua kemunculan kena.
Ya. =SUBSTITUTE("Jakarta";"jakarta";"JKT") nggak ngapa-ngapain. Kalau perlu cuek huruf, bungkus dulu pakai UPPER atau LOWER.
Pakai REPLACE: =REPLACE(A2;5;8;"xxxxxxxx"). Hasil dari 1234567812345678 jadi 1234xxxxxxxx5678. Cara paling cepat bikin data sensitif aman dibagikan.
Bisa. Isi penggantinya dengan string kosong: =SUBSTITUTE(A2;"-";""). Semua tanda hubung hilang. Ini cara paling umum bersihin nomor HP dan NPWP.
Yang perlu nempel:
Lanjut ke rumus FIND dan SEARCH Excel kalau kamu perlu tau posisi karakter dulu sebelum bisa nge-REPLACE.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai rumus FV dan PV Excel buat hitung nilai uang di masa depan dan sekarang. Lengkap dengan contoh tabungan dan target modal UMKM.
Investasi kelihatan untung di atas kertas, tapi nilai uang berubah tiap tahun. NPV dan IRR Excel bantu kamu nilai apakah proyek beneran layak, bukan cuma kelihatan untung.
Sebelum ambil pinjaman, kamu bisa hitung sendiri cicilan bulanannya pakai satu rumus. Ini cara PMT Excel bekerja, lengkap dengan contoh modal usaha dan jebakan bunga tahunan.