Contoh Excel Regex: Menggunakan ungkapan biasa dalam formula

Tidak pernah dapat memahami mengapa ungkapan biasa tidak disokong dalam formula Excel? Sekarang, mereka :) Dengan fungsi tersuai kami, anda boleh mencari, menggantikan, mengekstrak dan mengeluarkan rentetan yang sepadan dengan corak tertentu.

Pada pandangan pertama, Excel mempunyai semua yang anda perlukan untuk manipulasi rentetan teks. Hmm ... bagaimana dengan ungkapan biasa? Oops, tidak ada fungsi regex terbina dalam Excel. Tetapi tiada siapa yang mengatakan bahawa kita tidak boleh membuat orang kita sendiri :)

• Apa itu Regex?
• Adakah terdapat fungsi regex dalam excel?
• Lembaran Cheat Excel Regex
• Fungsi VBA Regex tersuai
• Fungsi perlawanan regex
• Fungsi ekstrak regex
• Regex Ganti Fungsi
• Alat Regex - Ekspresi Biasa dalam Excel Tanpa VBA

Apakah ungkapan biasa?

Ekspresi biasa (aka regex atau regexp ) adalah urutan aksara yang dikodkan khas yang mentakrifkan corak carian. Menggunakan corak itu, anda boleh mencari gabungan aksara yang sepadan dalam rentetan atau sahkan input data. Jika anda sudah biasa dengan notasi wildcard, anda boleh memikirkan Regexes sebagai versi lanjutan Wildcards.

Ekspresi biasa mempunyai sintaks mereka sendiri yang terdiri daripada watak -watak khas, pengendali, dan pembinaan. Sebagai contoh, [0-5] sepadan dengan mana-mana digit tunggal dari 0 hingga 5.

Ekspresi biasa digunakan dalam banyak bahasa pengaturcaraan termasuk JavaScript dan VBA. Yang terakhir ini mempunyai objek RegEXP khas, yang akan kami gunakan untuk membuat fungsi tersuai kami.

Adakah Excel menyokong Regex?

Malangnya, tidak ada fungsi regex terbina dalam Excel. Untuk dapat menggunakan ungkapan biasa dalam formula anda, anda perlu membuat fungsi yang ditentukan oleh pengguna anda sendiri (VBA atau .NET berasaskan) atau memasang alat pihak ketiga yang menyokong regexes.

Lembaran Cheat Excel Regex

Sama ada corak regex sangat mudah atau sangat canggih, ia dibina menggunakan sintaks biasa. Tutorial ini tidak bertujuan untuk mengajar anda ungkapan biasa. Untuk ini, terdapat banyak sumber dalam talian, dari tutorial percuma untuk pemula ke kursus premium untuk pengguna lanjutan.

Di bawah ini kami memberikan rujukan cepat kepada corak regex utama yang akan membantu anda memahami asas -asas. Ia juga boleh berfungsi sebagai lembaran cheat anda ketika mempelajari contoh selanjutnya.

Jika anda selesa dengan ekspresi biasa, anda boleh melompat terus ke fungsi regexp.

Watak

Ini adalah corak yang paling kerap digunakan untuk memadankan aksara tertentu.

Corak	Penerangan	Contoh	Perlawanan
.	Watak Wildcard: Memadankan mana -mana watak tunggal kecuali rehat garis	.t	titik , panas , periuk , @ot
\ d	Watak digit: Sebarang digit tunggal dari 0 hingga 9	\ d	Di A1b , perlawanan 1
\ D	Mana -mana watak yang bukan digit	\ D	Dalam A1b , sepadan dengan A dan B
\ s	Watak Whitespace: ruang, tab, barisan baru dan pulangan kereta	. \ s.	Dalam 3 sen , sepadan 3 c
\ S	Mana-mana watak bukan Whitespace	\ S	Dalam 30 sen , sepadan dengan 30 dan sen
\ w	Karakter Word: Surat, digit atau garis bawah ASCII	\ w	Dalam 5_cats *** , sepadan dengan 5_cats
\ W	Mana -mana watak yang bukan watak abjad angka atau garis bawah	\ W	Dalam 5_cats *** , perlawanan ***
\ t	Tab
\ n	Garis baru	\ n \ d	Dalam rentetan dua baris di bawah, sepadan dengan 10 5 kucing 10 anjing
\	Melarikan diri dari makna khas watak, jadi anda boleh mencarinya	\. \ w \.	Melarikan diri dari tempoh supaya anda dapat mencari literal "." Watak dalam rentetan Encik , Puan , Prof.

Kelas watak

Menggunakan corak ini, anda boleh memadankan unsur -unsur set aksara yang berbeza.

Corak	Penerangan	Contoh	Perlawanan
[aksara]	Sepadan dengan watak tunggal dalam kurungan	d [oi] g	anjing dan menggali
[^aksara]	Sepadan dengan watak tunggal yang tidak ada dalam kurungan	d [^oi] g	Perlawanan Dag, Dug , D1G tidak sepadan dengan anjing dan menggali
[dari -ke]	Memadankan watak dalam julat antara kurungan	[0-9] [AZ] [AZ]	Sebarang digit tunggal dari 0 hingga 9 mana -mana huruf kecil tunggal huruf besar

Kuantifier

Kuantifi adalah ungkapan khas yang menentukan bilangan aksara yang sepadan. Pengiraan sentiasa terpakai kepada watak sebelum itu.

Corak	Penerangan	Contoh	Perlawanan
*	Sifar atau lebih kejadian	1a*	1, 1A , 1AA, 1AAA , dll.
	Satu atau lebih kejadian	PO	Dalam periuk , sepadan dengan po di miskin , sepadan dengan poo
?	Sifar atau satu kejadian	Roa? D.	Jalan, Rod
*?	Sifar atau lebih banyak kejadian, tetapi lebih sedikit mungkin	1a*?	Dalam 1A , 1AA dan 1AAA , sepadan dengan 1A
?	Satu atau lebih kejadian, tetapi lebih sedikit mungkin	po?	Dalam periuk dan miskin , sepadan dengan po
?	Sifar atau satu kejadian, tetapi lebih sedikit mungkin	ROA ??	Di jalan dan batang , perlawanan ro
{n}	Memadankan corak sebelumnya n kali	\ d {3}	Tepat 3 digit
{n,}	Sepadan dengan corak sebelumnya atau lebih banyak kali	\ d {3,}	3 atau lebih digit
{n, m}	Memadankan corak terdahulu antara n dan m kali	\ d {3,5}	Dari 3 hingga 5 digit

Pengumpulan

Pembinaan kumpulan digunakan untuk menangkap substring dari rentetan sumber, jadi anda boleh melakukan beberapa operasi dengannya.

Sintaks	Penerangan	Contoh	Perlawanan
(corak)	Menangkap Kumpulan: Menangkap substring yang sepadan dan memberikannya nombor ordinal	(\ d)	Dalam 5 kucing dan 10 anjing , menangkap 5 (kumpulan 1) dan 10 (kumpulan 2)
(?: corak)	Kumpulan Tidak Penangkap: Memadankan kumpulan tetapi tidak menangkapnya	(\ d) (?: anjing)	Dalam 5 kucing dan 10 anjing , menangkap 10
\ 1	Kandungan Kumpulan 1	(\ d) \ (\ d) = \ 2 \ \ 1	Perlawanan 5 10 = 10 5 dan menangkap 5 dan 10 , yang dalam menangkap kumpulan
\ 2	Kandungan Kumpulan 2

Sauh

Sauh menentukan kedudukan dalam rentetan input di mana untuk mencari perlawanan.

Anchor	Penerangan	Contoh	Perlawanan
^	Permulaan rentetan Nota: [^di dalam kurungan] bermaksud "tidak"	^\ d	Mana -mana bilangan digit pada permulaan rentetan. Dalam 5 kucing dan 10 anjing , perlawanan 5
$	Akhir rentetan	\ d $	Mana -mana bilangan digit pada akhir rentetan. Dalam 10 ditambah 5 memberi 15 , perlawanan 15
\ b	Sempadan perkataan	\ bjoy \ b	Memadankan kegembiraan sebagai perkataan yang berasingan, tetapi tidak menyeronokkan .
\ B	Bukan sempadan perkataan	\ Bjoy \ b	Memadankan kegembiraan dalam keseronokan , tetapi bukan sebagai perkataan yang berasingan.

Penggantian (atau) membina

Pengendali alternasi membolehkan logik atau logik, jadi anda boleh memadankan sama ada elemen ini atau itu.

Membina	Penerangan	Contoh	Perlawanan
|	Memadankan mana -mana elemen tunggal yang dipisahkan oleh bar menegak	(s | sh) ells	Dalam dia menjual kerang laut, perlawanan dijual dan kerang

Look-arounds

Pembentukan lookaround membantu apabila anda ingin memadankan sesuatu yang atau tidak diikuti atau didahului oleh sesuatu yang lain. Ekspresi ini kadang-kadang dipanggil "pernyataan sifar lebar" atau "perlawanan sifar lebar" kerana mereka sepadan dengan kedudukan dan bukannya aksara sebenar.

Nota. Dalam rasa VBA Regex, lookbehinds tidak disokong.

Corak	Penerangan	Contoh	Perlawanan
(? =)	Lookahead positif	X (? = Y)	Perlawanan Ekspresi X Apabila diikuti oleh Y (iaitu jika ada Y di hadapan x)
(?!)	Lookahead negatif	X (?! Y)	Perlawanan Ekspresi X Jika tidak diikuti oleh Y
(?	Lookbehind positif	(?	Perlawanan Ekspresi X Apabila ia didahului oleh Y (iaitu jika ada Y di belakang x)
(?	Lookbehind negatif	(?	Perlawanan Ekspresi X Apabila ia tidak didahului oleh Y

Sekarang bahawa anda tahu perkara -perkara penting, mari kita beralih ke bahagian yang paling menarik - menggunakan regex pada data sebenar untuk menghuraikan rentetan dan mencari maklumat yang diperlukan. Jika anda memerlukan lebih banyak maklumat mengenai sintaks, Panduan Microsoft pada bahasa ekspresi biasa mungkin dapat membantu.

Fungsi Regex tersuai untuk Excel

Seperti yang telah disebutkan, Microsoft Excel tidak mempunyai fungsi regex terbina dalam. Untuk membolehkan ekspresi biasa, kami telah mencipta tiga fungsi VBA tersuai (fungsi yang ditetapkan pengguna). Anda boleh menyalin kod dari halaman berkaitan di bawah atau dari buku kerja sampel kami, dan kemudian tampal dalam fail Excel anda sendiri.

Bagaimana fungsi VBA Regexp berfungsi

Bahagian ini menerangkan mekanik dalaman dan mungkin menarik bagi mereka yang ingin mengetahui dengan tepat apa yang berlaku di backend.

Untuk mula menggunakan ungkapan biasa di VBA, anda perlu sama ada mengaktifkan Perpustakaan Rujukan Objek Regex atau menggunakan fungsi CreateObject. Untuk menyelamatkan anda masalah menetapkan rujukan dalam editor VBA, kami memilih pendekatan yang terakhir.

Objek RegEXP mempunyai 4 sifat:

• Corak - adalah corak yang sepadan dengan rentetan input.
• Global - mengawal sama ada untuk mencari semua perlawanan dalam rentetan input atau hanya yang pertama. Dalam fungsi kami, ia ditetapkan untuk mendapatkan semua perlawanan .
• Multiline - Menentukan sama ada untuk memadankan corak merentasi garis pecah dalam rentetan berbilang baris atau hanya pada baris pertama. Dalam kod kami, ia ditetapkan untuk benar untuk mencari dalam setiap baris .
• Ignorecase -mentakrifkan sama ada ungkapan biasa adalah sensitif kes (lalai) atau kes-insensitif (ditetapkan kepada benar). Dalam kes kami, itu bergantung pada cara anda mengkonfigurasi parameter Match_case pilihan. Secara lalai, semua fungsi adalah sensitif kes .

VBA REGEXP Batasan

Excel VBA melaksanakan corak regex penting, tetapi ia tidak mempunyai banyak ciri canggih yang terdapat dalam enjin .NET, Perl, Java, dan lain -lain. Sebagai contoh, VBA RegEXP tidak menyokong pengubahsuaian dalam talian seperti (? I) untuk pemadanan kes-insensitif atau (? M) untuk mod multi-line, lookbehinds, kelas POSIX, untuk menamakan beberapa.

Fungsi perlawanan excel regex

Fungsi regexpmatch mencari rentetan input untuk teks yang sepadan dengan ungkapan biasa dan mengembalikan benar jika perlawanan ditemui, sebaliknya.

Regexpmatch (teks, corak, [match_case])

Di mana:

• Teks (diperlukan) - Satu atau lebih rentetan untuk mencari.
• Corak (diperlukan) - Ungkapan biasa untuk dipadankan.
• Match_case (pilihan) - Jenis perlawanan. Benar atau ditinggalkan - kes sensitif; FALSE - Case -Insensitive

Kod fungsi ada di sini.

Contoh: Cara Menggunakan Ekspresi Biasa Untuk Memadankan Rentetan

Dalam dataset di bawah, katakan anda ingin mengenal pasti entri yang mengandungi kod SKU.

Memandangkan setiap SKU bermula dengan 2 huruf besar, diikuti dengan tanda hubung, diikuti dengan 4 digit, anda boleh memadankannya menggunakan ungkapan berikut.

Corak : \ b [az] {2}-\ d {4} \ b

Di mana [az] {2} bermaksud mana -mana 2 huruf besar dari a hingga z dan \ d {4} bermaksud mana -mana 4 digit dari 0 hingga 9. Satu sempadan perkataan \ b menunjukkan bahawa sku adalah perkataan yang berasingan dan bukan sebahagian daripada rentetan yang lebih besar.

Dengan corak yang ditubuhkan, mula menaip formula seperti yang biasanya anda lakukan, dan nama fungsi akan muncul dalam senarai yang dicadangkan oleh autocomplete Excel:

Dengan mengandaikan rentetan asal berada dalam A5, formula berjalan seperti berikut:

=RegExpMatch(A5, "\b[AZ]{2}-\d{3}\b")

Untuk kemudahan, anda boleh memasukkan ungkapan biasa dalam sel yang berasingan dan menggunakan rujukan mutlak ($ ​​A $ 2) untuk hujah corak . Ini memastikan bahawa alamat sel akan tetap tidak berubah apabila anda menyalin formula ke sel lain:

=RegExpMatch(A5, $A$2)

Untuk memaparkan label teks anda sendiri dan bukannya benar dan palsu, sarang regexpmatch dalam fungsi IF dan tentukan teks yang dikehendaki dalam argumen value_if_true dan value_if_false :

=IF(RegExpMatch(A5, $A$2), "Yes", "No")

Untuk lebih banyak contoh formula, sila lihat:

• Cara Memadankan Rentetan Menggunakan Ekspresi Biasa
• Pengesahan Data Excel dengan Regexes

Fungsi ekstrak excel regex

Fungsi RegexPExtract mencari substrings yang sepadan dengan ekspresi biasa dan mengekstrak semua perlawanan atau perlawanan tertentu.

RegExpExtract (teks, corak, [instance_num], [match_case])

Di mana:

• Teks (diperlukan) - Rentetan teks untuk dicari.
• Corak (diperlukan) - Ungkapan biasa untuk dipadankan.
• Instance_num (pilihan) - Nombor siri yang menunjukkan contoh yang hendak diekstrak. Jika ditinggalkan, pulangan semua perlawanan yang dijumpai (lalai).
• Match_case (pilihan) - mentakrifkan sama ada untuk memadankan (benar atau ditinggalkan) atau mengabaikan (palsu) kes teks.

Anda boleh mendapatkan kod fungsi di sini.

Contoh: Cara mengekstrak rentetan menggunakan ungkapan biasa

Mengambil contoh kami sedikit lebih jauh, mari kita mengeluarkan nombor invois. Untuk ini, kami akan menggunakan regex yang sangat mudah yang sepadan dengan nombor 7 digit:

Corak : \ b \ d {7} \ b

Letakkan corak dalam A2 dan anda akan mendapat pekerjaan yang dilakukan dengan formula yang padat dan elegan ini:

=RegExpExtract(A5, $A$2)

Sekiranya corak dipadankan, formula mengekstrak nombor invois, jika tiada perlawanan dijumpai - tiada apa yang dikembalikan.

Untuk lebih banyak contoh, sila lihat: Bagaimana untuk mengekstrak rentetan dalam Excel menggunakan regex.

Excel Regex Ganti Fungsi

Fungsi RegexPreplace menggantikan nilai yang sepadan dengan regex dengan teks yang anda tentukan.

RegExPreplace (teks, corak, penggantian, [instance_num], [match_case])

Di mana:

• Teks (diperlukan) - Rentetan teks untuk dicari.
• Corak (diperlukan) - Ungkapan biasa untuk dipadankan.
• Penggantian (diperlukan) - Teks untuk menggantikan substrings yang sepadan dengan.
• Instance_num (pilihan) - Contoh untuk menggantikan. Lalai adalah "semua perlawanan".
• Match_case (pilihan) - Kawalan sama ada untuk memadankan (benar atau ditinggalkan) atau mengabaikan (palsu) kes teks.

Kod fungsi tersedia di sini.

Contoh: Cara mengganti atau mengeluarkan rentetan menggunakan regexes

Beberapa rekod kami mengandungi nombor kad kredit. Maklumat ini sulit, dan anda mungkin mahu menggantikannya dengan sesuatu atau memadam sama sekali. Kedua -dua tugas boleh dicapai dengan bantuan fungsi regexpleplace . Bagaimana? Dalam senario kedua, kami akan menggantikan dengan rentetan kosong.

Dalam jadual sampel kami, semua nombor kad mempunyai 16 digit, yang ditulis dalam 4 kumpulan yang dipisahkan dengan ruang. Untuk mencari mereka, kami meniru corak menggunakan ungkapan biasa ini:

Corak : \ b \ d {4} \ d {4} \ d {4} \ d {4} \ b

Untuk penggantian, rentetan berikut digunakan:

Penggantian : xxxx xxxx xxxx xxxx

Dan inilah formula lengkap untuk menggantikan nombor kad kredit dengan maklumat yang tidak sensitif:

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "XXXX XXXX XXXX XXXX")

Dengan teks regex dan penggantian dalam sel berasingan (A2 dan B2), formula berfungsi dengan baik:

Dalam Excel, "mengeluarkan" adalah kes tertentu "menggantikan". Untuk mengeluarkan nombor kad kredit, gunakan rentetan kosong ("") untuk hujah pengganti :

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "")

Petua. Untuk mendapatkan rig garisan kosong dalam hasil, anda boleh menggunakan fungsi RegexPreplace yang lain seperti yang ditunjukkan dalam contoh ini: Cara Menghapus Talian Kosong Menggunakan Regex.

Untuk maklumat lanjut, sila lihat:

• Cara mengganti rentetan dalam excel menggunakan regex
• Cara mengeluarkan rentetan menggunakan regex
• Cara melepaskan ruang putih menggunakan regexes

Alat regex untuk dipadankan, mengekstrak, mengganti dan mengeluarkan substrings

Pengguna suite muktamad kami boleh mendapatkan semua kuasa ekspresi biasa tanpa memasukkan satu baris kod dalam buku kerja mereka. Semua kod yang diperlukan ditulis oleh pemaju kami dan smoothy bersepadu dalam Excel anda semasa pemasangan.

Tidak seperti fungsi VBA yang dibincangkan di atas, fungsi Ultimate Suite adalah .NET berdasarkan, yang memberikan dua kelebihan utama:

1. Anda boleh menggunakan ungkapan biasa dalam buku kerja .xlsx biasa tanpa menambah kod VBA dan perlu menyimpannya sebagai fail yang dibolehkan makro.
2. Enjin .NET Regex menyokong ekspresi biasa klasik yang lengkap, yang membolehkan anda membina corak yang lebih canggih.

Cara menggunakan regex dalam excel

Dengan suite muktamad yang dipasang, menggunakan ungkapan biasa dalam Excel adalah semudah kedua -dua langkah ini:

1. Pada tab Data Cabebits , dalam kumpulan teks , klik Alat Regex .

   

2. Pada anak tetingkap Regex Tools , lakukan perkara berikut:
   • Pilih data sumber.
   • Masukkan corak regex anda.
   • Pilih pilihan yang dikehendaki: perlawanan , ekstrak , keluarkan atau ganti .
   • Untuk mendapatkan hasilnya sebagai formula dan bukan nilai, pilih sisipan sebagai kotak semak formula .
   • Tekan butang Tindakan.

   Sebagai contoh, untuk mengeluarkan nombor kad kredit dari sel A2: A6, kami mengkonfigurasi tetapan ini:

   

Dalam trice, fungsi yang dapat dilancarkan akan dimasukkan ke dalam lajur baru di sebelah kanan data asal anda. Dalam kes kita, formula adalah:

=AblebitsRegexRemove(A2, "\b\d{4} \d{4} \d{4} \d{4}\b")

Sebaik sahaja formula ada, anda boleh mengedit, menyalin atau memindahkannya seperti mana -mana formula asli.

Cara memasukkan formula regex secara langsung di dalam sel

Fungsi AbleBitsRegex juga boleh dimasukkan terus ke dalam sel tanpa menggunakan antara muka Add-in. Inilah Caranya:

1. Klik butang FX pada bar formula atau masukkan fungsi pada tab Formula .
2. Dalam kotak dialog Sisipan Fungsi , pilih kategori Abdebitsudfs , pilih fungsi minat, dan klik OK.

   

3. Tentukan hujah fungsi seperti yang biasanya anda lakukan dan klik OK. Selesai!

Untuk maklumat lanjut, sila lihat Alat Regex untuk Excel.

Itulah cara menggunakan ungkapan biasa untuk dipadankan, mengekstrak, menggantikan dan mengeluarkan teks dalam sel Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Muat turun yang ada

Excel Regex - Contoh Formula (.xlsm Fail) Ultimate Suite - Versi Percubaan (.exe File)

Atas ialah kandungan terperinci Contoh Excel Regex: Menggunakan ungkapan biasa dalam formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!