=SUBSTITUTE(A2,"-","") menghapus setiap tanda hubung dari teks di A2: SUBSTITUTE mencari teks lama lalu menaruh teks baru di tempatnya, di sini teks kosong. =SUBSTITUTE(A2,"-",".") akan menaruh titik sebagai pengganti setiap tanda hubung.
| A | B | C | |
|---|---|---|---|
| 1 | Phone | Digits only | With dots |
| 2 | 555-201-3344 | 5552013344 | 555.201.3344 |
| 3 | 555-870-1122 | 5558701122 | 555.870.1122 |
| 4 | 555-433-9001 | 5554339001 | 555.433.9001 |
Hasilnya berupa teks, bahkan ketika yang tersisa hanya digit. Tambahkan -- di depannya, =--SUBSTITUTE(A2,"-",""), kalau kamu butuh angka.
SUBSTITUTE vs REPLACE
=SUBSTITUTE(text, old_text, new_text, [instance_num])
=REPLACE(old_text, start_num, num_chars, new_text)
SUBSTITUTE mencari isi: tidak peduli di mana old_text berada. REPLACE tidak mencari apa pun: REPLACE menghitung karakter dan menimpa sebanyak num_chars karakter mulai dari start_num.
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | Report 2025 final | Report 2026 final |
| 3 | Report 2025 final | Report 2026 final |
| 4 | ID-0042 | SKU-0042 |
| 5 | 5551234 | 555-1234 |
- B2 dan B3 memberi hasil yang sama, tapi B3 hanya berfungsi karena tahunnya dimulai di karakter ke-8. Kalau teksnya berubah, SUBSTITUTE tetap menemukannya; REPLACE tidak.
- B4 mengganti 2 karakter pertama, apa pun isinya, dengan
SKU. - B5 memakai panjang 0: tidak ada yang dihapus, jadi REPLACE menyisipkan tanda hubung setelah karakter ketiga. Itulah cara standar menyisipkan karakter di sebuah posisi.
Pakai SUBSTITUTE kalau kamu tahu teksnya, REPLACE kalau kamu tahu posisinya. Untuk pengeditan berdasarkan posisi, FIND bisa memberikan posisinya: =REPLACE(A2,FIND(" ",A2),1,"_") mengganti spasi pertama.
Ganti kemunculan kedua (ke-n) saja
Argumen keempat SUBSTITUTE memilih kemunculan mana yang diubah. Tanpa argumen itu, semuanya diubah:
| A | B | |
|---|---|---|
| 1 | Path | Result |
| 2 | 2026-03-15-final | 2026/03/15/final |
| 3 | 2026-03/15-final | |
| 4 | 2026-03-15 final |
B2 mengubah setiap tanda hubung. B3, dengan 2, hanya mengubah yang kedua dan memberi 2026-03/15-final; B4 menaruh spasi sebagai pengganti yang ketiga. Nomor kemunculan yang lebih besar dari jumlah kecocokan tidak mengubah apa pun.
| A | B | |
|---|---|---|
| 1 | Plan | New name |
| 2 | Basic monthly |
Giliranmu: Di B2, ganti Basic dengan Standard di nama paket di A2, dengan memakai rumus.
Hapus beberapa karakter yang berbeda
SUBSTITUTE menangani satu old_text per pemanggilan. Untuk beberapa karakter, tumpuk pemanggilannya: setiap pemanggilan bekerja pada hasil pemanggilan di dalamnya.
| A | B | |
|---|---|---|
| 1 | Phone | Digits |
| 2 | (555) 201-3344 | 5552013344 |
| 3 | (555) 870-1122 | 5558701122 |
Baca dari dalam ke luar: hapus (, lalu ), lalu -, lalu spasi. Di Microsoft 365, =REGEXREPLACE(A2,"[^0-9]","") menghapus setiap karakter yang bukan digit dalam satu pemanggilan. Khusus untuk spasi berlebih, TRIM adalah alat yang lebih tepat, karena menyisakan satu spasi di antara kata.
Hitung kemunculan sebuah karakter atau kata
SUBSTITUTE dengan teks kosong menghapus setiap kemunculan, jadi panjang yang hilang sama dengan jumlah karakter yang dihapus. Bagi dengan panjang katanya untuk menghitung jumlah kata:
| A | B | C | |
|---|---|---|---|
| 1 | Text | Commas | "red" appears |
| 2 | red, green, red, blue | 3 | 2 |
| 3 | Red, red, red | 2 | 2 |
C3 mengembalikan 2, bukan 3: SUBSTITUTE membedakan huruf besar kecil, jadi Red tidak dihapus. Pakai SUBSTITUTE(LOWER(A3),"red","") untuk menghitung tanpa memedulikan huruf besar kecil.
| A | B | |
|---|---|---|
| 1 | Tags | Count |
| 2 | excel,text,formulas,tips |
Giliranmu: Tag di A2 dipisahkan dengan koma. Di B2, hitung berapa banyak tag-nya (satu lebih banyak dari jumlah koma).
Kesalahan umum: SUBSTITUTE untuk mengubah angka
SUBSTITUTE bekerja pada teks dari sebuah angka, dan hasilnya berupa teks. =SUBSTITUTE(A2,"0","") pada 1050 memberi 15 sebagai teks, dan format seperti $1,050.00 diabaikan karena SUBSTITUTE membaca nilai yang disimpan, 1050. Untuk mengubah angka, pakai aritmetika. Untuk mengubah teks di banyak sel sekaligus tanpa rumus, Home > Find & Select > Replace (Ctrl+H; Control+H di Mac) punya pilihan Match case dan langsung mengganti di tempatnya.
Pertanyaan yang sering diajukan
Apa perbedaan SUBSTITUTE dan REPLACE di Excel?
SUBSTITUTE mencari teks berdasarkan isinya: =SUBSTITUTE(A2,"old","new") mengubah setiap old. REPLACE bekerja berdasarkan posisi: =REPLACE(A2,5,3,"new") menimpa 3 karakter mulai dari karakter ke-5, apa pun isinya.
Bagaimana cara menghapus sebuah karakter dari sel di Excel?
Ganti dengan teks kosong: =SUBSTITUTE(A2,"-","") menghapus setiap tanda hubung. Untuk beberapa karakter, tumpuk pemanggilannya: =SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","").
Bagaimana cara menghitung berapa kali sebuah karakter muncul di sel?
Bandingkan panjangnya sebelum dan sesudah karakter itu dihapus: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")) menghitung jumlah koma di A2. Untuk sebuah kata, bagi dengan panjang katanya: =(LEN(A2)-LEN(SUBSTITUTE(A2,"red","")))/3.
Apakah SUBSTITUTE membedakan huruf besar dan kecil?
Ya. =SUBSTITUTE("Red red","red","blue") memberi Red blue. Untuk mengabaikan huruf besar kecil, ubah teksnya ke huruf kecil dulu, =SUBSTITUTE(LOWER(A2),"red","blue"), atau pakai Find and Replace (Ctrl+H), yang secara bawaan mengabaikan huruf besar kecil.
