SUBSTITUTE
Diperbarui
SUBSTITUTE mengganti teks tertentu di dalam sebuah teks dengan teks baru. Pakai untuk membuang karakter yang tidak diinginkan, mengganti pemisah, atau membersihkan angka yang tersimpan sebagai teks. Secara bawaan semua kemunculan diganti, dan pencocokan membedakan huruf besar dan kecil.
Formula dasar
=SUBSTITUTE(text, old_text, new_text, [instance_num])
| Argumen | Arti | Wajib? |
|---|---|---|
text | Teks atau sel yang diproses | Ya |
old_text | Teks yang dicari untuk diganti (membedakan huruf besar dan kecil) | Ya |
new_text | Teks pengganti. Kosongkan ("") untuk menghapus | Ya |
instance_num | Kemunculan ke berapa yang diganti. Bila dikosongkan, semuanya | Tidak |
Contoh: mengganti kata yang berulang
A2 berisi satu dua satu, B2 berisi satu, C2 berisi tiga.
| Rumus | Hasil | Keterangan |
|---|---|---|
=SUBSTITUTE(A2, B2, C2) | tiga dua tiga | Semua satu diganti |
=SUBSTITUTE(A2, B2, C2, 2) | satu dua tiga | Hanya satu yang kedua |
=SUBSTITUTE(A2, B2, "") | dua | Menghapus satu (spasi di sekitarnya tetap) |
=SUBSTITUTE("Satu dua satu", "satu", "tiga") | Satu dua tiga | Satu berhuruf besar tidak cocok |
=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1 | 3 | Jumlah kata: selisih panjang = jumlah spasi (2), tambah 1 |
Contoh angka berformat Rp. Sel A3 berisi teks Rp 1.500.000:
| Rumus | Hasil |
|---|---|
=SUBSTITUTE(A3, ".", "") | Rp 1500000 |
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(A3, "Rp", ""), ".", ""))) | 1500000 |
Simulasi: Fungsi SUBSTITUTE
Ganti sebagian teks dengan teks lain. Pelajari SUBSTITUTE langkah demi langkah.
| A | B | C | |
|---|---|---|---|
| 1 | Teks | Lama | Baru |
| 2 | satu dua satu | satu | tiga |
| 4 | Hasil | tiga dua tiga | =SUBSTITUTE(teks, lama, baru) |
| 5 | Hanya ke-2 | satu dua tiga | Arg ke-4 = urutan keberapa |
Kolom biru = input yang bisa diedit. Ubah teks, yang diganti, atau pengganti.
Panah, Enter, dan Tab memindah sel. Esc mengembalikan sel ke isi awal.
Langkah demi langkah
1. Apa itu SUBSTITUTE?
SUBSTITUTE mengganti teks tertentu dengan teks baru: =SUBSTITUTE(teks, lama, baru, [urutan]). Case-sensitive.
2. Ganti Semua
B4 = SUBSTITUTE("satu dua satu", "satu", "tiga") = "tiga dua tiga". Semua kemunculan "satu" diganti.
3. Urutan Tertentu
B5 = SUBSTITUTE(..., 2) hanya mengganti kemunculan ke-2. Argumen ke-4 menentukan urutan (1-based).
4. Bersihkan Data
SUBSTITUTE cocok hapus karakter tak diinginkan: =SUBSTITUTE(A2, CHAR(160), " ") ganti non-breaking space jadi spasi biasa.
Pelajaran Kunci
SUBSTITUTE vs REPLACE: SUBSTITUTE by teks, REPLACE by posisi. Pakai SUBSTITUTE kalau tahu teksnya, REPLACE kalau tahu posisinya.
Kesalahan umum
| Gejala | Penyebab | Perbaikan |
|---|---|---|
| Tidak ada yang berubah | Huruf besar dan kecil tidak sama dengan old_text | Samakan huruf, atau ubah teks dulu dengan LOWER |
| Tidak ada yang berubah pada data dari web | Spasi tak terputus bukan spasi biasa | SUBSTITUTE(A2, UNICHAR(160), " ") |
| Hasil masih teks, tidak bisa dijumlahkan | SUBSTITUTE selalu menghasilkan teks | Bungkus dengan VALUE(...) |
| Terlalu banyak yang terganti | instance_num kosong | Isi nomor kemunculan |
#VALUE! | instance_num kurang dari 1 | Pakai bilangan bulat mulai 1 |
| Rumus ditolak | Pemisah regional Indonesia umumnya titik koma | =SUBSTITUTE(A2;B2;C2) |
Excel vs Google Sheets, dan alternatifnya
- SUBSTITUTE sama di Excel dan Google Sheets, termasuk sifat yang membedakan huruf besar dan kecil.
- Pencocokan berdasarkan pola (misalnya semua angka) tidak didukung. Di Google Sheets pakai REGEXREPLACE; di Excel pakai Flash Fill atau Power Query.
- Mengganti berdasarkan posisi: REPLACE. Hanya merapikan spasi: TRIM.
- Sekali jalan tanpa rumus: Find & Replace (Ctrl+H).
Sumber: SUBSTITUTE di Microsoft Support dan SUBSTITUTE di Google Docs Editors Help.
Tips
- Beberapa penggantian bisa ditumpuk:
=SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "")membuang tanda hubung dan spasi sekaligus. - Kombinasikan dengan LEN untuk menghitung berapa kali sebuah karakter muncul:
=LEN(A2)-LEN(SUBSTITUTE(A2, "a", "")). - Salin hasilnya, lalu tempel sebagai nilai sebelum menghapus kolom sumber.
Lihat juga
Pertanyaan yang sering muncul
Apa beda SUBSTITUTE dan REPLACE?
SUBSTITUTE mengganti berdasarkan isi teks (Anda tahu apa yang diganti), REPLACE berdasarkan posisi (Anda tahu di karakter ke berapa). Pakai SUBSTITUTE bila teks yang diganti bisa muncul di posisi berbeda-beda.
Apakah SUBSTITUTE membedakan huruf besar dan kecil?
Ya. =SUBSTITUTE("Satu dua satu", "satu", "tiga") hanya mengganti satu yang huruf kecil dan menghasilkan Satu dua tiga.
Bagaimana mengganti hanya kemunculan kedua?
Isi argumen ke-4 dengan nomor kemunculan: =SUBSTITUTE("satu dua satu", "satu", "tiga", 2) menghasilkan satu dua tiga.
Bagaimana memakai SUBSTITUTE untuk mengubah teks "Rp 1.500.000" menjadi angka?
Buang Rp dan titiknya, lalu ubah dengan VALUE: =VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(A2, "Rp", ""), ".", ""))) menghasilkan 1500000.