excel.my.id
TeksExcelGoogle Sheets

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])
ArgumenArtiWajib?
textTeks atau sel yang diprosesYa
old_textTeks yang dicari untuk diganti (membedakan huruf besar dan kecil)Ya
new_textTeks pengganti. Kosongkan ("") untuk menghapusYa
instance_numKemunculan ke berapa yang diganti. Bila dikosongkan, semuanyaTidak

Contoh: mengganti kata yang berulang

A2 berisi satu dua satu, B2 berisi satu, C2 berisi tiga.

RumusHasilKeterangan
=SUBSTITUTE(A2, B2, C2)tiga dua tigaSemua satu diganti
=SUBSTITUTE(A2, B2, C2, 2)satu dua tigaHanya satu yang kedua
=SUBSTITUTE(A2, B2, "")duaMenghapus satu (spasi di sekitarnya tetap)
=SUBSTITUTE("Satu dua satu", "satu", "tiga")Satu dua tigaSatu berhuruf besar tidak cocok
=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+13Jumlah kata: selisih panjang = jumlah spasi (2), tambah 1

Contoh angka berformat Rp. Sel A3 berisi teks Rp 1.500.000:

RumusHasil
=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.

B4 =SUBSTITUTE(A2, B2, C2)
Simulasi SUBSTITUTE. Sel biru bisa diubah, sel tebal berisi rumus.
ABC
1TeksLamaBaru
2satu dua satusatutiga
4Hasiltiga dua tiga=SUBSTITUTE(teks, lama, baru)
5Hanya ke-2satu dua tigaArg ke-4 = urutan keberapa

Kolom biru = input yang bisa diedit. Ubah teks, yang diganti, atau pengganti.

Langkah demi langkah
  1. 1. Apa itu SUBSTITUTE?

    SUBSTITUTE mengganti teks tertentu dengan teks baru: =SUBSTITUTE(teks, lama, baru, [urutan]). Case-sensitive.

    Lihat juga: REPLACE, FIND, TRIM

  2. 2. Ganti Semua

    B4 = SUBSTITUTE("satu dua satu", "satu", "tiga") = "tiga dua tiga". Semua kemunculan "satu" diganti.

  3. 3. Urutan Tertentu

    B5 = SUBSTITUTE(..., 2) hanya mengganti kemunculan ke-2. Argumen ke-4 menentukan urutan (1-based).

  4. 4. Bersihkan Data

    SUBSTITUTE cocok hapus karakter tak diinginkan: =SUBSTITUTE(A2, CHAR(160), " ") ganti non-breaking space jadi spasi biasa.

    Lihat juga: TRIM, FIND

  5. Pelajaran Kunci

    SUBSTITUTE vs REPLACE: SUBSTITUTE by teks, REPLACE by posisi. Pakai SUBSTITUTE kalau tahu teksnya, REPLACE kalau tahu posisinya.

    Lihat juga: REPLACE, FIND, MID

Kesalahan umum

GejalaPenyebabPerbaikan
Tidak ada yang berubahHuruf besar dan kecil tidak sama dengan old_textSamakan huruf, atau ubah teks dulu dengan LOWER
Tidak ada yang berubah pada data dari webSpasi tak terputus bukan spasi biasaSUBSTITUTE(A2, UNICHAR(160), " ")
Hasil masih teks, tidak bisa dijumlahkanSUBSTITUTE selalu menghasilkan teksBungkus dengan VALUE(...)
Terlalu banyak yang tergantiinstance_num kosongIsi nomor kemunculan
#VALUE!instance_num kurang dari 1Pakai bilangan bulat mulai 1
Rumus ditolakPemisah 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

REPLACE, TRIM, LEN, VALUE, LOWER, TEXT.

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.

Cari di excel.my.id

Coba cari