Rumus SUBSTITUTE dan REPLACE Excel: Ganti Teks Massal

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-5

Kalau kamu tau teks apa yang mau dibuang, pakai SUBSTITUTE. Kalau kamu cuma tau posisinya, pakai REPLACE.

Apa itu rumus SUBSTITUTE di Excel?

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/001

Angka 2 artinya: cuma tanda hubung kedua yang diganti.

Apa itu rumus REPLACE di Excel?

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")
→ 1234xxxxxxxx5678

Mulai 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.

Kapan pakai yang mana?

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.

Gimana cara rapikan nomor HP pakai SUBSTITUTE?

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.

Contoh kasus: alamat pengiriman toko_berkah

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.

Trik: hitung kemunculan karakter pakai SUBSTITUTE

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.

Kesalahan umum

• Ketuker SUBSTITUTE dan REPLACE. Gejalanya: kamu nulis =REPLACE(A2;"-";"") dan dapat #VALUE!. REPLACE minta angka posisi, bukan teks.
• Lupa SUBSTITUTE peka huruf besar-kecil. =SUBSTITUTE(A2;"jl";"Jalan") nggak kena "JL" atau "Jl". Seragamkan hurufnya dulu pakai LOWER atau PROPER.
• Ngarepin hasilnya masih angka. Hasil SUBSTITUTE selalu teks. Angka "1.500.000" yang titiknya dibuang jadi teks "1500000", bukan angka. Bungkus pakai VALUE kalau mau dijumlah.
• Tumpukan terlalu dalam. Lewat 5 lapis, rumusnya jadi mimpi buruk buat di-debug. Pecah jadi kolom bantu.
• Pakai REPLACE buat data yang panjangnya beda-beda. Posisi ke-5 di teks 10 karakter dan teks 20 karakter itu isinya beda. REPLACE cuma aman kalau formatnya fixed.

Referensi resmi kedua fungsi ini ada di dokumentasi SUBSTITUTE dari Microsoft.

Di SQL, cuma ada satu

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.

FAQ

Apa bedanya SUBSTITUTE dan REPLACE di Excel?

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.

Gimana cara ganti cuma kemunculan kedua di SUBSTITUTE?

Isi argumen keempat: =SUBSTITUTE(A2;"-";"/";2). Angka 2 artinya cuma tanda hubung kedua yang diganti. Kalau dikosongin, semua kemunculan kena.

Apakah SUBSTITUTE peka huruf besar kecil?

Ya. =SUBSTITUTE("Jakarta";"jakarta";"JKT") nggak ngapa-ngapain. Kalau perlu cuek huruf, bungkus dulu pakai UPPER atau LOWER.

Gimana cara sensor sebagian nomor kartu di Excel?

Pakai REPLACE: =REPLACE(A2;5;8;"xxxxxxxx"). Hasil dari 1234567812345678 jadi 1234xxxxxxxx5678. Cara paling cepat bikin data sensitif aman dibagikan.

SUBSTITUTE bisa hapus teks nggak, bukan cuma ganti?

Bisa. Isi penggantinya dengan string kosong: =SUBSTITUTE(A2;"-";""). Semua tanda hubung hilang. Ini cara paling umum bersihin nomor HP dan NPWP.

Penutup

Yang perlu nempel:

• Tau isinya → SUBSTITUTE. Tau posisinya → REPLACE.
• Argumen keempat SUBSTITUTE bikin kamu bisa target kemunculan tertentu.
• Hasil dua-duanya selalu teks. Bungkus VALUE kalau mau dihitung.

Lanjut ke rumus FIND dan SEARCH Excel kalau kamu perlu tau posisi karakter dulu sebelum bisa nge-REPLACE.