Cara memanfaatkan fungsi REGEX di Excel untuk meningkatkan operasi data

Jika Anda rutin bekerja dengan data di Excel, mempelajari cara menggunakan fungsi REGEX bisa sangat meningkatkan kemampuan Anda dalam menganalisis dan mengatur data. REGEX, atau ekspresi reguler, memberi Anda alat canggih untuk menemukan pola, memformat, dan mengekstrak informasi dari teks secara efisien. Dengan menggunakan fungsi ini di Excel, Anda bisa menyederhanakan banyak operasi kompleks seperti membersihkan data dan mengekstrak informasi yang relevan dari sel teks. Pada artikel ini, kami akan memperlihatkan cara menerapkan fungsi REGEX di Excel untuk meningkatkan pengelolaan data dan membuat tugas sehari-hari Anda lebih efisien.

Ekspresi reguler (atau REGEX) adalah pola pencarian yang dapat digunakan untuk memeriksa apakah string teks cocok dengan pola tertentu dan untuk mengekstrak atau mengganti string teks yang cocok dengan pola tertentu. Karena kerumitannya, artikel ini memberikan ringkasan sederhana dan contoh penggunaannya di Excel.

Memperhatikan
Fungsi REGEX tersedia bagi orang yang menggunakan Excel untuk Microsoft 365 di Windows atau Mac, serta mereka yang menggunakan Excel untuk web.

CARA MENGGUNAKAN REGEXTEST

Fungsi ini menguji apakah string teks cocok dengan pola tertentu, dan mengembalikan TRUE atau FALSE berdasarkan pengujian ini. Ini adalah cara yang bagus untuk menguji apakah data Anda mengikuti suatu pola.

sintaksis

UJI ULANG(a,b,c)

di mana

• a (wajib) adalah teks, nilai, atau referensi sel yang berisi teks yang ingin Anda uji,
• b (wajib) adalah pola yang digunakan untuk menjalankan pengujian, dan
• c (Opsional) 0 jika Anda ingin pengujian peka huruf besar-kecil, atau 1 jika tidak.

Contoh cara menggunakan REGEXTEST

Spreadsheet ini berisi daftar kode produk yang harus mengikuti struktur yang ketat.

Kode yang benar berisi:

• Representasi kecil dari ukuran produk (“xs” untuk ekstra kecil, “s” untuk kecil, “m” untuk sedang, dan seterusnya),
• Nomor satu atau dua digit yang mewakili bahan produk,
• Tiga huruf kapital yang mewakili tempat produk diproduksi, dan
• Tanda hubung di antara masing-masing dari tiga bagian yang ditunjukkan di atas.
• Saya ingin menguji apakah semua kode produk cocok dengan struktur ini.

Jadi, di sel B2, saya akan menulis:

=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)

di mana

• [@Code] adalah referensi terstruktur ke kolom tempat kode yang ingin saya uji berada,
• [xs|s|m|l|xl] adalah bagian pertama dari kode produk yang ingin saya uji, di mana garis vertikal berarti “atau”,
• [0-9]{1,2} adalah bagian kedua dari kode produk yang ingin saya uji, di mana [0-9] mewakili satu digit, dan {1,2} berarti bisa ada satu atau dua digit tunggal angka,
• [AZ]{3} adalah bagian ketiga dari kode produk yang ingin saya uji, dengan [AZ] mewakili huruf besar apa pun, dan {3} berarti harus ada tepat tiga huruf tersebut,
• Tiga bagian kode yang ingin saya uji dipisahkan dengan tanda hubung, dan
• 0 adalah argumen terakhir dalam rumus yang memberi tahu Excel bahwa pengujian peka huruf besar-kecil.

Saat saya menekan Enter untuk menerapkan rumus ini ke semua baris di kolom B, hasilnya hanya dua kode yang valid (BENAR).

m-2-UK tidak valid (ditunjukkan dengan hasil FALSE) karena kode negara hanya berisi dua huruf besar, xl-714-AUS tidak valid karena kode bahan berisi tiga digit, dan S-5-USA tidak valid karena kode ukuran adalah huruf besar.

Memperhatikan
Contoh ini berisi penggunaan karakter seperti [ ] dan {}. Namun, ada banyak karakter lain (juga dikenal sebagai simbol) yang juga dapat digunakan untuk mengidentifikasi pola yang digunakan untuk melakukan pengujian, beberapa di antaranya akan saya gunakan pada contoh di bawah.

REGEXEXTRACT: Mencari bagian teks tertentu
Fungsi ini mengembalikan bagian teks dalam sel menurut pola yang ditentukan. Misalnya, Anda mungkin ingin memisahkan angka dan teks.

sintaksis

EKSTRAK REGEX(d,e,f,g)

di mana

• d (wajib) adalah teks, nilai, atau referensi sel yang berisi teks yang ingin Anda ekstrak,
• e (wajib) adalah pola yang ingin Anda ekstrak,
• f (opsional) sama dengan 0 jika Anda ingin mengekstrak hanya kecocokan pertama, 1 untuk mengekstrak semua kecocokan yang berlaku sebagai array, 2 untuk mengekstrak tupel dari kecocokan pertama, dan
• g (Opsional) Sama dengan 0 jika Anda ingin ekstraksi peka huruf besar-kecil, atau 1 jika tidak.

Memperhatikan
Karena tabel Excel yang diformat tidak bisa menangani larik yang tumpah, jika Anda ingin mengekstrak kecocokan sebagai larik dalam argumen f, pastikan data Anda diformat secara eksplisit.

Contoh cara menggunakan REGEXTRACT

Dalam contoh ini, saya ingin mengekstrak nama depan dan belakang serta nomor telepon pelanggan ke dalam tiga kolom terpisah.

Mari kita fokus pada namanya dulu. Di sel B2, saya akan mengetik:

=REGEXEXTRACT(A2,"[AZ][az]+",1)

di mana

• A2 adalah sel berisi data yang ingin saya ekstrak,
• [AZ][az]+ memberi tahu Excel bahwa saya ingin mengekstrak kata apa pun yang dimulai dengan huruf besar diikuti dengan huruf kecil, dengan “+” menunjukkan bahwa saya ingin mengembalikan satu atau lebih huruf kecil di setiap pola, dan
• 1 menunjukkan bahwa saya ingin memisahkan setiap contoh pola di atas ke dalam sel individual sebagai array (dengan kata lain, nama depan ada di sel B2, nama kedua ada di sel C2). Jika Anda menghilangkan argumen ini, Excel hanya mengembalikan kecocokan pertama (nama depan) di sel B2.

Ketika saya menekan Enter, Excel berhasil melakukan ekstraksi dan menambahkan garis biru pucat di sekitar sel C2 untuk mengingatkan saya bahwa itu adalah matriks pencar.

Dengan memilih sel B2, saya sekarang dapat menggunakan gagang isian di sudut kanan bawah sel untuk menduplikasi rumus relatif ini ke baris detail yang tersisa.

Sekarang, saya perlu menggunakan rumus REGEXTRACT serupa untuk mengekstrak nomor telepon pelanggan. Di sel D2, saya akan mengetik:

=REGEXEXTRACT(A2,"[0-9()]+ [0-9-]+")

di mana

• A2 adalah sel berisi data yang ingin saya ekstrak,
• [0-9()]+ mengekstrak angka dari nol hingga sembilan yang diapit tanda kurung bulat, dengan “+” diekstraksi untuk satu atau lebih angka dalam pola ini, dan
• [0-9-]+ mengekstrak nomor yang tersisa dari string, dengan “-” kedua mewakili tanda hubung yang memisahkan dua bagian nomor telepon, dan “+” memberi tahu Excel bahwa saya ingin mengekstrak satu atau lebih nomor jika string berisi mereka.

Karena hanya ada satu contoh pola ini di setiap sel di kolom A, saya tidak perlu menambahkan argumen lagi. Sekali lagi, setelah saya memverifikasi bahwa rumus ini menghasilkan hasil yang diharapkan, saya bisa menggunakan gagang isian untuk mengulanginya di sel yang tersisa di kolom D.

Memperhatikan
Ada cara lain di Excel untuk mengekstrak data dan mencapai hasil serupa, seperti menggunakan fungsi TEXTSPLIT atau alat Flash Fill Excel.

Manipulasi data menggunakan REGEXREPLACE

Fungsi ini mengambil teks dalam satu sel dan membuat salinan baru dari data tersebut di sel lain. Meskipun fungsi ini disebut REGEXREPLACE, namun tidak menggantikan teks asli di lokasi aslinya.

sintaksis

GANTI ULANG(h,i,j,k,l)

di mana

• h (wajib) adalah teks, nilai, atau referensi sel yang berisi teks yang ingin Anda ganti,
• i (wajib) adalah pola yang ingin Anda ganti,
• j (wajib) adalah pengganti yang ingin Anda buat,
• k (opsional) adalah frekuensi pola yang ingin Anda ganti, dan
• l (Opsional) Baik 0 jika Anda ingin penggantiannya peka huruf besar-kecil, atau 1 jika tidak.

Contoh cara menggunakan REGEXREPLACE

Di bawah ini Anda dapat melihat daftar nama-nama di Kolom A. Tujuan saya adalah membuat ulang nama-nama tersebut di Kolom B, tetapi menggunakan format “Nama Belakang, Nama Depan”, termasuk koma yang memisahkan nama-nama tersebut.

Di sel B2 saya akan menulis:

=REGEXREPLACE([@Nama klien],"([AZ][az]+) ([AZ][az]+)","$2, $1")

di mana

• [@Nama klien] menunjuk ke kolom yang berisi data yang ingin saya pengaruhi,
• [AZ][az]+ double tertanam dalam rumus (dan dipisahkan dengan spasi) memberi tahu Excel bahwa saya ingin mengambil dua string teks yang berisi huruf besar diikuti oleh satu atau lebih huruf kecil, dan
• $2, $1 memberi tahu Excel bahwa saya ingin membalik urutan dua string teks yang dipisahkan oleh koma dan spasi. Jika saya tidak menyertakan simbol dolar, Excel hanya akan mengembalikan “2, 1” sebagai hasilnya di setiap sel.

Saya tidak membahas argumen k dan l dalam rumus di atas karena saya ingin Excel mengganti semua kemunculan (default untuk k), dan saya ingin penggantinya peka huruf besar-kecil (default untuk l).

Karena saya menggunakan tabel yang diformat, ketika saya menekan Enter, rumusnya akan diterapkan ke sel yang tersisa di kolom B.

Penggunaan ekspresi reguler tidak terbatas pada Excel. Faktanya, Anda dapat menggunakan REGEX untuk mengotomatisasi tugas-tugas lain di komputer Anda, seperti memperbaiki teks PDF yang disalin dan ditempel, mengganti nama file yang diunduh secara massal, memformat mata uang, menghapus tag HTML, dan banyak lagi.