Lompat ke konten
Home » Blog » Regex di Excel: Kode untuk Mencari, Membersihkan, dan Mengambil Teks dengan Praktis dan Cepat

Regex di Excel: Kode untuk Mencari, Membersihkan, dan Mengambil Teks dengan Praktis dan Cepat

Share :

Regex (regular expression) adalah salah satu fitur baru di dalam pengolahan teks di Excel.

Pengolahan teks di Excel umum mengandalkan kombinasi LEFT, RIGHT, MID, FIND, SUBSTITUTE. Dengan regex, kita hanya perlu menulis satu pola untuk:

  • mencari teks dengan pola tertentu,
  • validasi format email atau nomor telepon,
  • membersihkan data yang berantakan,
  • mengambil bagian tertentu dari teks.

Artikel ini membahas:

  • konsep dasar regex,
  • simbol‑simbol penting regex,
  • contoh-contoh rumus dengan penjelasan,
  • perbedaan regex vs wildcard di Excel.

File: regex-excel.xlsx

Apa Itu Regex di Excel?

Regex (regular expression) adalah bahasa atau ekspresi untuk mendeskripsikan pola teks.

Alih‑alih menulis “Cari 5 digit angka di akhir kalimat yang mungkin diawali huruf dan spasi,” kita menulis \b\d{5}\b.

Fitur dan Fungsi Excel yang Mendukung Regex

Regex di Excel bisa digunakan di:

  • Fungsi bawaan (Microsoft 365). Contohnya: REGEXTEST, REGEXEXTRACT, REGEXREPLACE, XLOOKUP.
  • Power Query.
  • VBA / Office Scripts.
  • LAMBDA + Named Function.

Kapan Menggunakan Regex di Excel?

Regex sangat berguna ketika:

  • Mencari data spesifik, seperti kode produk, email dengan domain tertentu, teks yang mengandung pola karakter khusus.
  • Membersihkan data. Contohnya: menghapus karakter non angka dari nomor telepon, menghapus gelar dari nama, menghapus simbol dari kode.
  • Mengubah format data secara otomatis. Standarisasi nomor telepon, mengambil nama depan, memecah alamat menjadi area-area tertentu.

Tips Menggunakan Regex

  • Pelajari setiap kode dasar dan fungsinya.
  • Selalu mulai dari pola sederhana, lalu tingkatkan sesuai kebutuhan.
  • Selalu uji regex di data sampel sebelum diterapkan ke data utama.
  • Manfaatkan situs online untuk menguji dan belajar pola regex, seperti regex101.com.
  • Baca dokumentasi Excel atau kunjungi forum online jika ada masalah.
  • Jangan copy paste kode regex dari internet tanpa pemahaman, apalagi langsung diterapkan di data utama.

Daftar Simbol Regex Dasar dan Fungsinya

KategoriKarakterKeterangan
Penanda posisi (Anchors)^Awal teks atau baris
$Akhir teks atau baris
Pengulang (Quantifiers)*Nol atau lebih
+Satu atau lebih
?Nol atau satu kali
{n,m}Antara n dan m kali
Kelompok karakter (Character Sets).Karakter apa pun, kecuali newline
[abc]Salah satu dari a, b, atau c
[^abc]Karakter apa pun, kecuali a, b, atau c
[a-z]Huruf a sampai z
Singkatan (Shorthand)\dAngka 1 sampai 9
 \DBukan angka
\wHuruf, angka, underscore
\sWhitespace. Contoh: spasi, tab, newline
\bBatas kata atau word boundary
Logika (Logic)``
(...)Menempatkan sekelompok karakter sebagai satu unit
 |Kondisi OR (atau) di dalam satu pola. Contoh: m|n berarti m atau n.

Contoh Rumus Regex di Excel

Mengambil Kode Pos dari Alamat

Perusahaan logistik ingin mengambil kode pos 5 digit dari alamat lengkap.

=REGEXEXTRACT(A2; "\d{5}")

Penjelasan:

  • \d → satu digit angka dari 0 sampai 9
  • {5} → tepat 5 kali
  • \d{5} → cari 5 digit angka

Cara kerja:

  • Excel membaca teks di A2, misalnya “Jl. Melati No. 10, Jakarta 12345“.
  • REGEXEXTRACT mencari pola \d{5} atau 5 digit angka di dalam teks.

Jika dalam satu teks ada lebih dari satu kelompok 5 digit, Excel biasanya mengambil kecocokan pertama.

Mengambil kode pos dari alamat.

Membersihkan Nomor Seluler dari Karakter Non Angka

Nomor seluler datang dalam format yang tidak standar: ada spasi, tanda minus, tanda kurung, bahkan karakter lain. Untuk menyisakan hanya angka, gunakan rumus berikut:

=REGEXREPLACE(A2; "\D"; "")

Penjelasan:

  • \D → karakter yang bukan angka (kebalikan dari \d)

Cara kerja:

  • Excel membaca teks di A2, misalnya: “+62 812-3456-7890”.
  • REGEXREPLACE mencari semua karakter yang cocok dengan \D, seperti + (tanda tambah), spasi, atau – (minus).
  • Setiap kecocokan diganti dengan teks kosong (“”).
Membersihkan nomor seluler dari nomor bukan angka dengan regex.

Cara Sederhana Memeriksa Validitas Alamat Email

Tim sales ingin tahu apakah email calon pelanggan valid atau tidak. Kriteria yang digunakan adalah email minimal punya @ dan titik setelah @.

=IF(REGEXTEST(A2; ".+@.+\..+"); "Ya"; "Tidak")

Penjelasan:

  • . → satu karakter apa pun
  • + → diulang minimal satu kali
  • .+ → satu karakter atau lebih, karakter apa pun
  • @ → karakter @ secara harafiah (dituliskan langsung)
  • \. → titik secara harafiah (. harus di‑escape dengan \ untuk menghindari . memiliki kemampuan regex)

Jadi pola ini berarti:

  • .+ → ada satu atau lebih karakter sebelum @
  • @ → ada tanda @
  • .+ → ada satu atau lebih karakter setelah @
  • \. → ada satu titik (.)
  • .+ → ada satu atau lebih karakter setelah titik

Cara kerja:

  • Jika A2 berisi “user@example.com” → cocok → hasil “Ya”.
  • Jika A2 berisi “userexample.com” (tanpa @) → tidak cocok → hasil “Tidak”.
  • Jika A2 berisi “user@examplecom” (tanpa .) → tidak cocok → hasil “Tidak”.
Validasi alamat email dengan regex.

Catatan: Kode regex di atas hanya validasi email sederhana. Untuk validasi sesungguhnya, gunakan kode yang lebih kompleks.

Mengambil Nama Depan dari Nama Lengkap

HR ingin membuat name tag hanya dengan nama depan.

=REGEXEXTRACT(A2; "^[A-Za-z]+")

Penjelasan:

  • ^ → mulai dari awal teks
  • [A-Za-z] → satu huruf (A sampai Z atau a sampai z)
  • + → satu atau lebih

Jadi pola ini berarti: Mulai dari awal teks, ambil semua huruf berturut‑turut sampai ketemu karakter yang bukan huruf.

Cara kerja:

  • “Budi Santoso” → mengembalikan “Budi” karena spasi bukan huruf
  • “Sari Lestari, S.Pd” → mengembalikan “Sari”
Mengambil nama depan dari nama lengkap.

Membuang Gelar dari Nama

Di universitas, nama dosen sering ditulis dengan gelar. Untuk database, HR hanya ingin menyimpan nama tanpa gelar.

=TRIM(
   SUBSTITUTE(
      REGEXREPLACE(
         A2;
         "(Dr\.|M\.Si\.|Prof\.|M\.Pd\.|S\.E\.|M\.M\.)";
         "");
      ",";
      ""
   )
)

Penjelasan (Dr\\.|M\\.Si\\.|Prof\\.|M\\.Pd\\.|S\\.E\\.|M\\.M\\.):

  • (...) → kelompok
  • | → kondisi atau
  • Dr\. → teks “Dr.” (titik di‑escape agar tidak keliru dengan titik tanda regex)
  • M\.Si\. → “M.Si.”
  • dan seterusnya

Jadi pola ini berarti: “Cari salah satu dari: Dr., M.Si., Prof., M.Pd., S.E., M.M.”

Langkah di Excel:

  • REGEXREPLACE menghapus semua gelar yang cocok dengan pola.
  • SUBSTITUTE(...; ","; "") menghapus koma yang tersisa.
  • TRIM merapikan spasi berlebih di awal atau akhir.
Membuang gelar dari nama dengan regex.

Mengambil Nama Domain atau Perusahaan dari Email

Perusahaan percetakan ingin tahu domain atau perusahaan dari alamat email klien untuk kategori database.

=REGEXEXTRACT(A2; "@([^/ ]+)")

Penjelasan:

  • @ → cari karakter @
  • (...) → kelompok yang ingin diambil
  • [^/ ] → karakter apa pun kecuali slash atau spasi
  • + → satu karakter atau lebih

Jadi pola ini berarti: “Mulai dari @, ambil semua karakter setelahnya selama bukan slash atau spasi.”

Mengambil nama domain atau perusahaan dari email.

Memberi Kategori Produk Berdasarkan Nama

Supervisor ingin mengelompokkan produk ke kategori berdasarkan kata kunci di nama produk. Untuk itu, supervisor mendaftar kemungkinan nama kategori di dalam tabel tersendiri. Sebagai contoh, detergen memiliki kemungkinan disebut sebagai detergen, deterjen, atau bahkan rinso.

=XLOOKUP(
   TRUE;
   REGEXTEST(A2; $E$2:$E$5);
   $D$2:$D$5;
   "Lainnya"
)

Penjelasan:

  • (?i) → membuat pencarian case‑insensitive, tidak peduli huruf besar atau kecil.
  • | → kondisi atau. detergent|detergen|deterjen|rinso berarti detergent, detergen, deterjen, atau rinso

Cara kerja:

  • REGEXTEST(A2; $E$2:$E$5) menghasilkan array TRUE atau FALSE, tergantung apakah nama produk di A2 cocok dengan salah satu pola di E2:E5.
  • XLOOKUP mencari nilai TRUE pertama di array tersebut.
  • Jika ketemu, mengembalikan kategori di D2:D5. Jika tidak, mengembalikan “Lainnya”.
Memberi kategori pada produk berdasarkan nama.

Standarisasi Nomor Seluler Indonesia ke Format +62

Nomor seluler datang dalam berbagai format: 0812, +62 812, 62-812, dan sebagainya. Untuk memformatnya ke dalam standar +62812, gunakan rumus berikut:

=CONCAT(
   "+62";
   REGEXREPLACE(A2; "^\+?62|[^0-9]"; "")
)

Kode regex di atas terdiri dari 2 bagian, masing-masing terpisahkan dengan | (kondisi atau):

  • ^+?62
    • ^ → mulai dari awal teks
    • +? → tanda + yang boleh ada atau tidak (?)
    • 62 → angka 62. Artinya: hapus awalan 62 atau +62 di awal nomor.
  • [^0-9]
    • […] → kelompok karakter.
    • ^ sebagai karakter pertama di dalam [] berarti NOT, TIDAK, atau BUKAN
    • [^0-9] → semua karakter yang bukan angka.

Cara kerja di Excel:

  • REGEXREPLACE menghapus awalan +62 atau 62 di awal, lalu menghapus semua karakter non angka.
  • CONCAT("+62"; ...) menambahkan kembali +62 di depan.
Standarisasi nomor seluler Indonesia menjadi format +62.

Kesalahan Umum Regex dan Solusinya

MasalahPenyebabSolusi
Pola tidak cocok sama sekaliPenulisan regex salah, ada salah tulis, atau pola tidak sesuai struktur dataUji pola di sampel kecil.
Gunakan situs seperti regex101.
Pecah pola menjadi bagian kecil untuk diuji satu per satu.
Regex terlalu ketat atau terlalu longgarPola menangkap terlalu banyak atau terlalu sedikit dataSesuaikan quantifier.
Tambahkan batasan seperti ^, $, atau [...].
Ulangi pola sampai hasil stabil.
Lupa escape karakter khususKarakter seperti ., +, ?, () atau ` dianggap simbol regex, bukan karakter harafiahGunakan tanda escape \ untuk mendapatkan karakter harafiah
Hasil regex tidak sesuai ekspektasiPola tidak mempertimbangkan variasi format data (spasi, simbol, huruf besar atau kecil)Gunakan flag seperti (?i) untuk case‑insensitive.
Tambahkan alternatif dengan `.
Perjelas kelompok karakter.
Fungsi regex tidak tersediaExcel versi lama belum mendukung fungsi-fungsi regexGunakan Power Query, VBA, atau upgrade ke Microsoft 365
Pengambilan karakter salah karena greedy matchingQuantifier seperti .+ menangkap terlalu banyak karakterGunakan versi non‑greedy seperti .+? atau batasi dengan anchor dan kelompok karakter
Regex lambat di dataset besarPola terlalu kompleks atau dipakai di ribuan barisSederhanakan pola; gunakan helper column; hindari quantifier berat seperti .* jika tidak perlu
Data tidak dibersihkan sebelum regexAda spasi tersembunyi, karakter non‑printable, atau format tidak konsistenGunakan TRIM, CLEAN, atau SUBSTITUTE sebelum menerapkan regex

Perbedaan Regex dengan Wildcard

AspekRegexWildcard
KetersediaanHanya di fungsi baru seperti TEXTSPLIT, TEXTAFTER, TEXTBEFORE, REGEXTEST, REGEXREPLACE, REGEXEXTRACT, XLOOKUPTersedia di fungsi seperti COUNTIF, SUMIF, SEARCH, MATCH
Simbol yang digunakanLebih beragamTerbatas
Tingkat kompleksitasTinggiRendah
Validasi format?YaTidak
Bisa menangkap kelompok karakter?YaTidak
Bisa ekstraksi teks?Ya (REGEXEXTRACT)Tidak
Bisa menggantikan pola?Ya (REGEXREPLACE)Tidak
Bisa digunakan di SUMIF/COUNTIF?TidakYa
Bisa digunakan di FILTER?Ya (dengan regex)Ya (dengan wildcard)
KecepatanLebih lambat (lebih berat)Lebih cepat (lebih ringan)
Kapan dipakai?Saat butuh logika pola rumitSaat butuh pencarian cepat dan simpel

Kesimpulan

Regex di Excel membuka cara baru untuk bekerja dengan teks: lebih ekspresif, lebih fleksibel, dan jauh lebih kuat dibanding kombinasi fungsi teks klasik.

Kunci menguasainya tidak dengan menghafal semua simbol, tapi:

  • memahami beberapa pola dasar
  • membiasakan diri membaca pola pelan‑pelan
  • selalu menguji di contoh kecil sebelum diterapkan ke data besar

Tinggalkan Balasan

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *