Cara Menggunakan Fungsi Regex di Excel

Lihat cepat fungsi ekspresi biasa Excel: regextest, regexextract dan regexreplace

Ekspresi biasa (regex) adalah corak carian yang digunakan untuk memeriksa sama ada rentetan teks mematuhi corak yang diberikan dan untuk mengekstrak atau menggantikan rentetan teks yang sepadan dengan corak yang diberikan. Memandangkan kerumitannya, artikel ini memberikan ringkasan ringkas dan contoh penggunaannya dalam Excel.

Fungsi

regex sesuai untuk pengguna yang menggunakan Microsoft 365 Excel untuk Windows atau Mac, serta mereka yang menggunakan Excel di web.

regextest: ujian pencocokan corak teks

Fungsi ini menguji sama ada rentetan teks sepadan dengan corak yang diberikan dan pulangan benar atau palsu berdasarkan hasil ujian. Ini adalah cara yang baik untuk menguji sama ada data mengikuti corak tertentu.

Tatabahasa

<code>REGEXTEST(a, b, c)</code>

of:

• a
b
• (diperlukan) adalah mod yang digunakan untuk melakukan ujian.
c
• (pilihan) 0 Jika anda mahu ujian menjadi sensitif kes;

Regextest Contoh Gunakan

Spreadsheet ini mengandungi senarai kod produk yang mesti mengikuti struktur yang ketat.

kod kilang termasuk:

Perwakilan kes yang lebih rendah dari saiz produk ("XS" bermaksud sangat kecil, "S" bermaksud kecil, "m" bermaksud sederhana, dll),

mewakili satu atau dua digit bahan produk,
tiga huruf besar yang mewakili lokasi pembuatan produk, dan
Tiga bahagian dipisahkan oleh garis mendatar pendek.
• kita perlu menguji sama ada semua kod produk sepadan dengan struktur ini.

Jadi, dalam sel B2, masukkan formula berikut:

of:

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

adalah rujukan berstruktur pada lajur yang mengandungi kod yang akan diuji.

• [@Code] adalah bahagian pertama kod produk yang akan diuji, dan garis menegak menunjukkan "atau".
• [xs|s|m|l|xl] adalah bahagian kedua kod produk yang akan diuji,
bermaksud mana -mana satu digit, dan
• bermaksud terdapat satu atau dua digit. [0-9]{1,2} [0-9] {1,2} adalah bahagian ketiga kod produk yang akan diuji,
bermaksud mana -mana huruf besar, dan
• bermaksud mesti ada tiga huruf besar. [A-Z]{3} [A-Z] Tiga bahagian kod yang akan diuji dipisahkan oleh garis mendatar pendek. {3}
0 adalah parameter terakhir dalam formula, yang memberitahu Excel untuk menguji kepekaan kes.
• Selepas menekan Enter untuk memohon formula ini kepada semua baris dalam lajur B, hasilnya menunjukkan bahawa hanya dua kod yang sah (benar).

• M-2-UK tidak sah (ditunjukkan oleh hasil palsu) kerana kod negara mengandungi hanya dua huruf besar.
• XL-714-AUS tidak sah kerana kod bahan mengandungi tiga digit.
• S-5-USA tidak sah kerana kod saiz dipermodalkan.

Contoh ini mengandungi penggunaan aksara seperti [] dan {}. Walau bagaimanapun, terdapat banyak watak lain (juga dipanggil penanda) yang juga boleh digunakan untuk menentukan corak yang digunakan untuk melakukan ujian, beberapa di antaranya akan digunakan dalam contoh -contoh di bawah.

RegexExtract: Ekstrak serpihan teks spesifik

Fungsi ini mengembalikan sebahagian teks dalam sel mengikut corak yang ditentukan. Sebagai contoh, anda mungkin mahu memisahkan nombor dan teks.

Tatabahasa

<code>REGEXTEST(a, b, c)</code>

of:

• d (diperlukan) ialah teks, nilai, atau rujukan sel dari mana teks itu akan diekstrak.
• e (diperlukan) adalah corak yang akan diekstrak.
• f (Pilihan) 0 Jika anda mahu mengekstrak hanya perlawanan pertama;
• g (pilihan) 0 Jika anda mahu pengekstrakan menjadi sensitif kes;

Oleh kerana jadual Excel yang diformat tidak dapat mengendalikan tatasusunan limpahan, jika anda bercadang untuk mengekstrak perlawanan sebagai array dalam parameter

f , pastikan data anda dalam format normal.

RegexExtract Contoh Gunakan

Dalam contoh ini, nama dan nombor telefon pelanggan perlu diekstrak ke dalam tiga lajur berasingan.

Fokus pertama pada nama. Dalam sel B2, masukkan formula berikut:

of:

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

A2 adalah sel yang mengandungi data yang akan diekstrak.

Beritahu Excel untuk mengekstrak sebarang perkataan yang bermula dengan huruf besar diikuti dengan huruf kecil, di mana "" bermaksud untuk mengembalikan satu atau lebih huruf kecil dalam setiap corak.
• [A-Z][a-z] 1 bermaksud anda ingin memisahkan setiap contoh corak di atas ke dalam sel individu sebagai array (dengan kata lain, nama pertama berada dalam sel B2 dan nama kedua dalam sel C2). Jika parameter ini ditinggalkan, Excel hanya mengembalikan perlawanan pertama (nama pertama) dalam sel B2.
Selepas menekan Enter, Excel melakukan pengekstrakan dengan jayanya dan menambah garis biru cahaya di sekitar C2 C2 untuk mengingatkan anda bahawa ia adalah array limpahan.

Selepas memilih sel B2, kini anda boleh menyalin formula relatif ini ke baris butiran yang tinggal menggunakan pemegang isi di sudut kanan bawah sel.

Sekarang, formula regextract yang sama perlu digunakan untuk mengekstrak nombor telefon pelanggan. Dalam sel D2, masukkan formula berikut:

of:

• A2 adalah sel yang mengandungi data yang akan diekstrak.
• [0-9()] Nombor dari sifar hingga sembilan ekstrak nombor dalam kurungan, di mana "" mengekstrak satu atau lebih nombor dalam corak ini.
• [0-9-] Ekstrak nombor yang tersisa dalam rentetan. akan diekstrak.

Oleh kerana hanya ada satu contoh corak ini dalam setiap sel dalam lajur A, tiada parameter tambahan perlu ditambah. Begitu juga, sebaik sahaja anda menyemak bahawa formula ini menghasilkan hasil yang diharapkan, anda boleh menggunakan pemegang isi untuk menyalinnya ke sel yang tinggal di lajur D.

Terdapat cara lain dalam Excel yang mengekstrak data dan mendapatkan hasil yang sama, seperti menggunakan fungsi TextSplit atau alat isi cepat Excel.

RegexReplace: Data Operasi

Fungsi ini mengambil teks dalam sel dan mencipta versi baru data dalam sel lain. Walaupun fungsi itu dipanggil RegexReplace, ia sebenarnya tidak menggantikan teks asal dalam kedudukan asalnya.

Tatabahasa

<code>REGEXTEST(a, b, c)</code>

of:

• h (diperlukan) ialah teks, nilai, atau rujukan sel kepada teks yang akan diganti.
• i (diperlukan) adalah mod yang akan diganti.
J
• (diperlukan) adalah kandungan penggantian yang akan dibuat.
k
• (pilihan) adalah bilangan kejadian corak yang akan diganti.
l
• (Pilihan) 0 Jika anda mahu penggantian menjadi sensitif kes;

RegexReplace Contoh penggunaan

di bawah, anda dapat melihat satu siri nama dalam lajur A. Matlamatnya adalah untuk mencipta semula nama -nama ini dalam lajur B, tetapi gunakan format "nama belakang, pertama", termasuk koma yang berasingan.

Dalam sel B2, masukkan formula berikut:

of:

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

merujuk kepada lajur yang mengandungi data untuk mempengaruhi.

• [@Client name] Termasuk dua kali dalam formula (dan dipisahkan oleh ruang), beritahu Excel untuk mendapatkan dua rentetan teks yang mengandungi huruf besar diikuti oleh satu atau lebih huruf kecil.
• [A-Z][a-z] Beritahu Excel untuk membalikkan susunan kedua -dua rentetan teks ini dan memisahkannya dengan koma dan ruang. Jika tanda dolar tidak termasuk, Excel hanya akan kembali "2, 1" sebagai hasil bagi setiap sel.
• , Parameter
k

dan

l > tidak diproses dalam formula di atas, kerana anda mahu Excel menggantikan semua kejadian (nilai lalai parameter 🎜>) dan anda mahu menggantikan kes sensitif (nilai lalai untuk parameter l ). Oleh kerana jadual diformat digunakan, formula akan digunakan pada sel -sel yang tinggal di lajur B selepas menekan Enter.

---

Ekspresi biasa tidak hanya digunakan dalam Excel. Sebenarnya, anda boleh menggunakan Regex untuk melaksanakan tugas-tugas lain secara automatik pada komputer anda, seperti menetapkan teks PDF salinan-paste, kumpulan penamaan semula fail yang dimuat turun, memformat mata wang, mengeluarkan tag HTML, dan banyak lagi.

Atas ialah kandungan terperinci Cara Menggunakan Fungsi Regex di Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!