
Tautan Cepat
-
REGEXEXTRACT: Temukan Potongan Teks Tertentu
-
Memanipulasi Data dengan REGEXREPLACE
Ekspresi reguler (atau REGEX) adalah pola pencarian yang dapat digunakan untuk memeriksa apakah string teks sesuai dengan pola tertentu dan mengekstrak atau mengganti string teks yang cocok dengan pola tertentu. Mengingat kerumitannya, artikel ini menawarkan ringkasan sederhana dan contoh penggunaannya di Excel.
Fungsi REGEX tersedia untuk 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, mengembalikan TRUE atau FALSE berdasarkan pengujian ini. Ini adalah cara yang bagus untuk menguji apakah data Anda mengikuti pola tertentu.
Sintaksis
REGEXTEST(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 melakukan tes, dan
- C (opsional) bernilai 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 valid berisi:
- Representasi huruf 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 besar yang mewakili tempat produk diproduksi, dan
- Tanda hubung di antara masing-masing dari tiga bagian yang dijelaskan di atas.
Saya ingin menguji apakah semua kode produk cocok dengan struktur ini.
Jadi, di sel B2, saya akan mengetik:
=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, dengan garis vertikal yang berarti “atau”,
- [0-9]{1,2} adalah bagian kedua dari kode produk yang ingin saya uji [0-9] berupa satu digit apa pun, dan {1,2} artinya bisa ada satu atau dua digit tunggal,
- [A-Z]{3} adalah bagian ketiga dari kode produk yang ingin saya uji [A-Z] berupa huruf besar apa pun, dan {3} artinya harus ada tiga huruf berikut,
- 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-Inggris tidak valid (ditunjukkan dengan hasil FALSE) karena kode negara hanya berisi dua huruf besar,
- xl-714-AUS tidak valid karena kode material mengandung tiga digit, dan
- S-5-USA tidak valid karena kode ukurannya huruf besar.
Fungsi ini mengembalikan bagian teks dalam sel menurut pola yang ditentukan. Misalnya, Anda mungkin ingin memisahkan angka dan teks.
Sintaksis
REGEXEXTRACT(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) adalah 0 jika Anda ingin mengekstrak kecocokan pertama saja, 1 untuk mengekstrak semua kecocokan yang berlaku sebagai array, dan 2 untuk mengekstrak grup dari kecocokan pertama, dan
- G (opsional) bernilai 0 jika Anda ingin ekstraksi peka huruf besar/kecil, atau 1 jika tidak.
Dalam contoh ini, saya ingin mengekstrak nama depan, nama belakang, dan nomor telepon klien ke dalam tiga kolom terpisah.
Mari kita fokus pada namanya dulu. Di sel B2, saya akan mengetik:
=REGEXEXTRACT(A2,"[A-Z][a-z]+",1)
Di mana
- A2 adalah sel yang berisi data yang ingin saya ekstrak,
- [A-Z][a-z]+ memberi tahu Excel bahwa saya ingin mengekstrak kata apa pun yang dimulai dengan huruf kapital diikuti dengan huruf kecil, dengan “+” menunjukkan bahwa saya ingin mengembalikan satu atau lebih huruf kecil di setiap pola, dan
- 1 menunjukkan bahwa saya ingin setiap contoh pola di atas dipisahkan menjadi sel individual sebagai array (dengan kata lain, nama depan di sel B2, dan nama kedua di sel C2). Jika saya menghilangkan argumen ini, Excel akan mengembalikan kecocokan pertama (nama depan) hanya di sel B2.
Saat saya menekan Enter, Excel berhasil melakukan ekstraksi dan menambahkan garis biru samar di sekitar sel C2 untuk mengingatkan saya bahwa itu adalah array tumpah.
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 klien. Di sel D2, saya akan mengetik:
=REGEXEXTRACT(A2,"[0-9()]+ [0-9-]+")
Di mana
- A2 adalah sel yang berisi data yang ingin saya ekstrak,
- [0-9()]+ mengekstrak digit dari nol hingga sembilan yang berada di dalam tanda kurung bulat, dengan “+” mengekstraksi satu atau lebih digit dalam pola ini, dan
- [0-9-]+ mengekstrak digit 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 digit jika string berisi digit tersebut.
Karena hanya ada satu contoh pola ini di setiap sel di kolom A, saya tidak perlu menambahkan argumen lagi. Sekali lagi, setelah saya memeriksa bahwa rumus ini menghasilkan hasil yang diharapkan, saya bisa menggunakan gagang isian untuk menduplikasinya ke sel yang tersisa di kolom D.
Memanipulasi Data dengan REGEXREPLACE
Fungsi ini mengambil teks dalam sel dan membuat versi baru dari data tersebut di sel lain. Meski namanya REGEXREPLACE, fungsinya sebenarnya tidak menggantikan teks asli di lokasi aslinya.
Sintaksis
REGEXREPLACE(h,i,j,k,l)
Di mana
- H (wajib) adalah teks, nilai, atau referensi sel yang berisi teks yang ingin Anda ganti,
- Saya (wajib) adalah pola yang ingin Anda ganti,
- J (wajib) adalah pengganti yang ingin Anda buat,
- k (opsional) adalah kemunculan pola yang ingin Anda ganti, dan
- aku (opsional) bernilai 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 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 mengetik:
=REGEXREPLACE([@Client name],"([A-Z][a-z]+) ([A-Z][a-z]+)","$2, $1")
Di mana
- [@Client name] mereferensikan kolom yang berisi data yang ingin saya pengaruhi,
- [A-Z][a-z]+ dimasukkan dua kali dalam rumus (dan dipisahkan dengan spasi) memberi tahu Excel bahwa saya ingin mengambil dua rangkaian teks yang berisi huruf kapital diikuti oleh satu atau lebih huruf kecil, dan
- $2, $1 memberi tahu Excel bahwa saya ingin membalik urutan kedua rangkaian teks tersebut, dipisahkan dengan koma dan spasi. Jika saya tidak menyertakan simbol dolar, Excel hanya akan mengembalikan “2, 1” sebagai hasil di setiap sel.
Saya belum membahas argumen k Dan aku dalam rumus di atas karena saya ingin Excel mengganti semua kemunculan (default untuk argumen k), dan saya ingin penggantinya peka huruf besar-kecil (default untuk argumen aku).
Karena saya menggunakan tabel yang diformat, ketika saya menekan Enter, rumusnya akan diterapkan ke sel yang tersisa di kolom B.
Ekspresi reguler tidak hanya untuk digunakan di Excel. Faktanya, Anda dapat menggunakan REGEX untuk mengotomatiskan tugas-tugas lain di komputer Anda, seperti memperbaiki teks PDF yang disalin, mengganti nama file yang diunduh secara massal, memformat mata uang, menghapus tag HTML, dan banyak lagi.