Regex untuk mengekstrak rentetan dalam Excel (satu atau semua perlawanan)

Dalam tutorial ini, anda akan belajar bagaimana menggunakan ungkapan biasa dalam Excel untuk mencari dan mengekstrak substrings yang sepadan dengan corak yang diberikan.

Microsoft Excel menyediakan beberapa fungsi untuk mengekstrak teks dari sel. Fungsi tersebut boleh mengatasi kebanyakan cabaran pengekstrakan rentetan dalam lembaran kerja anda. Kebanyakan, tetapi tidak semua. Apabila fungsi teks tersandung, ungkapan biasa datang untuk menyelamatkan. Tunggu ... Excel tidak mempunyai fungsi regex! Benar, tiada fungsi terbina. Tetapi tidak ada yang menghalang anda daripada menggunakan yang anda sendiri :)

• Fungsi ekstrak VBA Regexp
  • Ekstrak nombor dari rentetan
  • Dapatkan semua perlawanan
  • Dapatkan alamat e -mel dari rentetan
  • Ekstrak domain e -mel
  • Ekstrak nombor telefon
  • Tarikh ekstrak
  • Pilih masa
• Fungsi ekstrak regex net
  • Ekstrak teks antara dua aksara
  • Ekstrak teks antara dua rentetan
  • Dapatkan nama domain dari URL

Excel VBA Regex Fungsi untuk mengekstrak rentetan

Untuk menambah fungsi ekstrak regex adat ke Excel anda, tampal kod berikut dalam editor VBA. Untuk membolehkan ekspresi biasa di VBA, kami menggunakan objek Microsoft RegEXP terbina dalam. Kod ini ditulis oleh pakar Excel kami Alex Frolov.

Fungsi awam regexpExtract (teks sebagai rentetan , corak sebagai rentetan , instance_num pilihan sebagai integer = 0 , match_case pilihan sebagai boolean = true ) text_matches ( ) sebagai rentetan rentetan . True regex.multiline = true jika true = match_case kemudian regex.ignorecase = false else regex.ignorecase = end true jika set matches = regex.execute (teks) jika 0 ( 0 = instance_num ) text_matches (matches_index, 0) = matches.item (matches_index) matches_index regexpextract = text_matches lain regexpextract = matches.item (instance_num - 1 ) akhir jika akhir jika keluar fungsi errhandl :

Jika anda mempunyai sedikit pengalaman dengan VBA, panduan pengguna langkah demi langkah boleh membuktikan: bagaimana untuk memasukkan kod VBA dalam Excel.

Nota. Untuk fungsi berfungsi, pastikan anda menyimpan fail anda sebagai buku kerja yang dibolehkan makro (.xlsm).

Sintaks regexpextract

Fungsi RegexPExtract mencari rentetan input untuk nilai yang sepadan dengan ekspresi biasa dan mengekstrak satu atau semua perlawanan.

Fungsi ini mempunyai sintaks berikut:

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

Di mana:

• Teks (diperlukan) - Rentetan teks untuk dicari.
• Corak (diperlukan) - Ungkapan biasa untuk dipadankan. Apabila dibekalkan secara langsung dalam formula, corak harus disertakan dalam tanda petikan berganda.
• 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 atau mengabaikan kes teks. Jika benar atau ditinggalkan (lalai), padanan sensitif kes dilakukan; Jika palsu - kes -insensitif.

Fungsi ini berfungsi dalam semua versi Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 dan Excel 2010.

4 perkara yang perlu anda ketahui mengenai regexpextract

Untuk menggunakan fungsi dengan berkesan dalam Excel anda, terdapat beberapa perkara penting untuk mengambil perhatian:

1. Secara lalai, fungsi mengembalikan semua perlawanan yang dijumpai ke dalam sel -sel jiran seperti yang ditunjukkan dalam contoh ini. Untuk mendapatkan kejadian tertentu, berikan nombor yang sepadan dengan argumen Instance_num .
2. Secara lalai, fungsi itu sensitif kes . Untuk pemadanan kes-insensitif, tetapkan argumen match_case kepada palsu. Kerana batasan VBA, pembentukan kes-insensitif (? I) tidak akan berfungsi.
3. Jika corak yang sah tidak dijumpai , fungsi tidak mengembalikan apa -apa (rentetan kosong).
4. Jika coraknya tidak sah , #value! ralat berlaku.

Sebelum anda mula menggunakan fungsi tersuai ini dalam lembaran kerja anda, anda perlu memahami apa yang mampu, bukan? Contoh -contoh di bawah meliputi beberapa kes penggunaan biasa dan terangkan mengapa tingkah laku mungkin berbeza dalam array dinamik Excel (Microsoft 365 dan Excel 2021) dan tradisional Excel (2019 dan versi lama).

Nota. Contoh Regex ditulis untuk set data mudah dongeng. Kami tidak dapat menjamin mereka akan bekerja dengan sempurna dalam lembaran kerja sebenar anda. Mereka yang mempunyai pengalaman dengan Regex akan bersetuju bahawa menulis ungkapan biasa adalah jalan yang tidak pernah berakhir untuk kesempurnaan - hampir selalu ada cara untuk menjadikannya lebih elegan atau mampu mengendalikan pelbagai data input yang lebih luas.

Regex untuk mengekstrak nombor dari rentetan

Berikutan pepatah asas pengajaran "dari Simple to Complex", kami akan bermula dengan kes yang sangat jelas: mengeluarkan nombor dari rentetan.

Perkara pertama untuk anda memutuskan ialah nombor yang akan diambil: Pertama, terakhir, kejadian khusus atau semua nombor.

Ekstrak nombor pertama

Ini semudah Regex boleh mendapatkan. Memandangkan \ d bermaksud mana -mana digit dari 0 hingga 9, dan bermaksud satu atau lebih kali, ungkapan biasa kami mengambil bentuk ini:

Corak : \ d

Tetapkan instance_num hingga 1 dan anda akan mendapat hasil yang diinginkan:

=RegExpExtract(A5, "\d ", 1)

Di mana A5 adalah rentetan asal.

Untuk kemudahan, anda boleh memasukkan corak dalam sel yang telah ditetapkan ($ A $ 2) dan mengunci alamatnya dengan tanda $:

=RegExpExtract(A5, $A$2, 1)

Dapatkan nombor terakhir

Untuk mengekstrak nombor terakhir dalam rentetan, inilah corak untuk digunakan:

Corak : (\ d) (?!.*\ D)

Diterjemahkan ke dalam bahasa manusia, ia berkata: Cari nombor yang tidak diikuti (di mana sahaja, bukan hanya dengan segera) oleh mana -mana nombor lain. Untuk menyatakan ini, kita menggunakan lookahead negatif (?!*\ D), yang bermaksud bahawa di sebelah kanan corak tidak ada digit lain (\ d) tanpa mengira berapa banyak watak lain sebelum itu.

=RegExpExtract(A5, "(\d )(?!.*\d)")

Petua:

• Untuk mendapatkan kejadian tertentu , gunakan \ d untuk corak dan nombor siri yang sesuai untuk Instance_num .
• Formula untuk mengekstrak semua nombor dibincangkan dalam contoh seterusnya.

Regex untuk mengekstrak semua perlawanan

Menolak contoh kami sedikit lebih jauh, katakan anda ingin mendapatkan semua nombor dari rentetan, bukan hanya satu.

Seperti yang anda ingat, bilangan perlawanan yang diekstrak dikawal oleh argumen Instance_num pilihan. Lalai adalah semua perlawanan, jadi anda hanya meninggalkan parameter ini:

=RegExpExtract(A2, "\d ")

Formula ini berfungsi dengan baik untuk sel tunggal, tetapi tingkah laku berbeza dalam versi Excel dan bukan dinamik dinamik.

Excel 365 dan Excel 2021

Oleh kerana sokongan untuk tatasusunan dinamik, formula biasa secara automatik menumpahkan ke dalam seberapa banyak sel yang diperlukan untuk memaparkan semua hasil yang dikira. Dari segi Excel, ini dipanggil pelbagai tumpah:

Excel 2019 dan lebih rendah

Dalam pra-dinamik Excel, formula di atas akan kembali hanya satu perlawanan. Untuk mendapatkan pelbagai perlawanan, anda perlu menjadikannya formula array. Untuk ini, pilih pelbagai sel, taipkan formula, dan tekan CTRL Shift Enter untuk menyelesaikannya.

Kelemahan pendekatan ini adalah sekumpulan kesilapan #N/A yang terdapat dalam "Sel Tambahan". Malangnya, tiada apa yang boleh dilakukan mengenainya (Iferror atau Ifna tidak boleh memperbaikinya, malangnya).

Ekstrak semua perlawanan dalam satu sel

Apabila memproses lajur data, pendekatan di atas jelas tidak akan berfungsi. Dalam kes ini, penyelesaian yang ideal akan mengembalikan semua perlawanan dalam satu sel. Untuk melakukannya, melayani hasil regexpExtract ke fungsi textjoin dan memisahkannya dengan mana -mana pembatas yang anda suka, katakan koma dan ruang:

=TEXTJOIN(", ", TRUE, RegExpExtract(A5, "\d "))

Nota. Kerana fungsi TextJoin hanya tersedia dalam Excel untuk Microsoft 365, Excel 2021 dan Excel 2019, formula itu tidak akan berfungsi dalam versi lama.

Regex untuk mengekstrak teks dari rentetan

Mengekstrak teks dari rentetan alfanumerik adalah tugas yang mencabar dalam Excel. Dengan Regex, ia menjadi semudah pai. Hanya gunakan kelas yang ditolak untuk memadankan semua yang bukan digit.

Corak : [^\ d]

Untuk mendapatkan substrings dalam sel individu (pelbagai tumpahan), formula adalah:

=RegExpExtract(A5, "[^\d] ")

Untuk mengeluarkan semua perlawanan ke dalam satu sel, sarang fungsi regexpextract dalam textjoin seperti ini:

=TEXTJOIN("", TRUE, RegExpExtract(A5, "[^\d] "))

Regex untuk mengekstrak alamat e -mel dari rentetan

Untuk mengeluarkan alamat e -mel dari rentetan yang mengandungi banyak maklumat yang berbeza, tulis ungkapan biasa yang mereplikasi struktur alamat e -mel.

Corak : [\ w \. \-] @[a-z0-9 \. \-] \. [A-za-z] {2,24}

Memecahkan regex ini, inilah yang kita dapat:

• [\ w \. \-] adalah nama pengguna yang mungkin termasuk 1 atau lebih aksara alfanumerik, garis bawah, titik dan tanda hubung.
• @ simbol
• [A-Za-Z0-9 \. \-] adalah nama domain yang terdiri daripada: huruf besar dan huruf kecil, digit, tanda hubung dan titik (dalam kes subdomain). Underscores tidak dibenarkan di sini, oleh itu 3 set aksara yang berbeza (seperti AZ AZ dan 0-9) digunakan bukannya \ w yang sepadan dengan mana-mana huruf, digit atau garis bawah.
• \. [A-za-z] {2,24} adalah domain peringkat atas. Terdiri daripada titik diikuti dengan huruf besar dan huruf kecil. Kebanyakan domain peringkat atas adalah 3-huruf panjang (misalnya .com .org, .edu, dan lain-lain), tetapi dalam teori ia boleh mengandungi 2 hingga 24 huruf (TLD yang paling lama didaftarkan).

Dengan mengandaikan rentetan berada dalam A5 dan corak dalam A2, formula untuk mengekstrak alamat e -mel adalah:

=RegExpExtract(A5, $A$2)

Regex untuk mengekstrak domain dari e -mel

Ketika datang untuk mengekstrak domain e -mel, pemikiran pertama yang masuk ke dalam fikiran adalah menggunakan kumpulan penangkapan untuk mencari teks yang segera mengikuti aksara @.

Corak : @([A-Za-Z0-9 \. \-] \. [A-Za-Z] {2,24})

Hidangkannya ke fungsi regexp kami:

=RegExpExtract(A5, "@([A-Za-z0-9\.\-] \.[A-Za-z]{2,24})")

Dan anda akan mendapat hasil ini:

Dengan ungkapan biasa klasik, apa -apa di luar kumpulan penangkapan tidak termasuk dalam pengekstrakan. Tiada siapa yang tahu mengapa VBA Regex berfungsi dengan cara yang berbeza dan menangkap "@" juga. Untuk menghilangkannya, anda boleh mengeluarkan watak pertama dari hasilnya dengan menggantikannya dengan rentetan kosong.

=REPLACE(RegExpExtract(A5, "@([az\d][az\d\-\.]*\.[az]{2,})", 1, FALSE), 1, 1, "")

Ungkapan biasa untuk mengekstrak nombor telefon

Nombor telefon boleh ditulis dalam pelbagai cara, itulah sebabnya ia tidak mungkin untuk menghasilkan penyelesaian yang bekerja di bawah semua keadaan. Walau bagaimanapun, anda boleh menulis semua format yang digunakan dalam dataset anda dan cuba memadankannya.

Untuk contoh ini, kami akan membuat regex yang akan mengekstrak nombor telefon dalam mana -mana format ini:

(123) 345-6789 (123) 345 6789 (123) 3456789 123-345-6789

123.345.6789 123 345 6789 1233456789

Corak : \ (? \ D {3} [-\. \)]*\ D {3} [-\. ]? \ d {4} \ b

• Bahagian pertama \ (? \ D {3} sepadan dengan sifar atau satu kurungan pembukaan diikuti oleh tiga digit d {3}.
• [-\. \)]* Bahagian bermaksud apa -apa watak dalam kurungan persegi yang muncul 0 atau lebih kali: Hyphen, Tempoh, Ruang atau Penutupan kurungan.
• Seterusnya, kami mempunyai tiga digit lagi d {3} diikuti oleh sebarang tanda hubung, tempoh atau ruang [-\. ]? muncul 0 atau 1 kali.
• Selepas itu, terdapat sekumpulan empat digit \ d {4}.
• Akhirnya, terdapat sempadan perkataan \ b yang menentukan bahawa nombor telefon yang kami cari tidak boleh menjadi sebahagian daripada nombor yang lebih besar.

Formula lengkap mengambil bentuk ini:

=RegExpExtract(A5, "\(?\d{3}[-\. \)]*\d{3}[-\. ]?\d{4}\b")

Harap maklum bahawa regex di atas boleh mengembalikan beberapa hasil positif palsu seperti 123) 456 7899 atau (123 456 7899. Versi di bawah ini membetulkan isu-isu ini.

Corak : (\ (\ d {3} \) | \ d {3}) [-\. ]? \ d {3} [-\. ]? \ d {4} \ b

Regex untuk mengekstrak tarikh dari rentetan

Ungkapan biasa untuk mengekstrak tarikh bergantung pada format di mana tarikh muncul dalam rentetan. Contohnya:

Untuk mengekstrak tarikh seperti 1/1/21 atau 01/01/2021, regex adalah: \ d {1,2} \/\ d {1,2} \/(\ d {4} | \ d {2})

Ia mencari sekumpulan 1 atau 2 digit d {1,2} diikuti oleh slash, diikuti oleh kumpulan lain 1 atau 2 digit, diikuti dengan slash, diikuti oleh sekumpulan 4 atau 2 digit (\ d {4} | \ d {2}). Sila ambil perhatian bahawa kami mencari 4 tahun pertama, dan hanya selama 2 digit tahun. Jika kita menulis sebaliknya, hanya 2 digit pertama yang akan dipilih dari semua tahun. Ini kerana selepas keadaan pertama dalam penggantian atau pembinaan dipadankan, keadaan yang selebihnya tidak diperiksa.

Untuk mengambil tarikh seperti 1-Jan-21 atau 01-Jan-2021, coraknya ialah: \ d {1,2}-[a-za-z] {3}-\ d {2,4}

Ia mencari sekumpulan 1 atau 2 digit, diikuti dengan tanda hubung, diikuti oleh sekumpulan 3 huruf besar atau huruf kecil, diikuti dengan tanda hubung, diikuti oleh sekumpulan 4 atau 2 digit.

Selepas menggabungkan kedua -dua corak bersama -sama, kami mendapat regex berikut:

Corak : \ b \ d {1,2} [\/-] (\ d {1,2} | [a-za-z] {3}) [\/-] (\ d {4} | \ d {2}) \ b

Di mana:

• Bahagian pertama ialah 1 atau 2 digit: \ d {1,2}
• Bahagian kedua sama ada 1 atau 2 digit atau 3 huruf: (\ d {1,2} | [a-za-z] {3})
• Bahagian ketiga adalah kumpulan 4 atau 2 digit: (\ d {4} | \ d {2})
• Delimiter sama ada slash ke hadapan atau tanda hubung: [\/-]
• Satu sempadan perkataan \ b diletakkan di kedua -dua belah pihak untuk menjelaskan bahawa tarikh adalah perkataan yang berasingan, dan bukan sebahagian daripada rentetan yang lebih besar.

Seperti yang anda lihat dalam imej di bawah, ia berjaya mengeluarkan tarikh dan meninggalkan substrings seperti 11/22/333. Walau bagaimanapun, ia masih mengembalikan keputusan positif palsu. Dalam kes kami, substring 11-ABC-2222 dalam A9 secara teknikal sepadan dengan format tarikh DD-MMM-Yyyy dan oleh itu diekstrak.

Untuk menghapuskan positif palsu, anda boleh menggantikan bahagian [a-za-z] {3} dengan senarai penuh singkatan bulan 3 huruf:

Corak : \ b \ d {1,2} [\/-] (\ d {1,2} | (Jan | feb | mar | apr | may | jul | aug | Sep | oct | nov | dec)) [\/-] (\ d {4} |

Untuk mengabaikan kes surat, kami menetapkan hujah terakhir fungsi tersuai kami kepada palsu:

=RegExpExtract(A5, $A$2, 1, FALSE)

Dan kali ini, kami mendapat hasil yang sempurna:

Regex untuk mengekstrak masa dari rentetan

Untuk mendapatkan masa dalam format HH: MM atau HH: MM: SS , ungkapan berikut akan berfungsi.

Corak : \ b (0? [0-9] | 1 [0-2]): [0-5] \ d (: [0-5] \ d)? \ S? (Am | pm) \ b |

Memecahkan regex ini, anda dapat melihat 2 bahagian yang dipisahkan oleh | yang membolehkan atau logik. Dalam erti kata lain, kami mencari substring yang sepadan dengan salah satu daripada ungkapan di bawah.

Ungkapan 1 : \ b (0? [0-9] | 1 [0-2]): [0-5] \ d (: [0-5] \ d)? \ S? (Am | pm) \ b

Mendapatkan masa dengan AM/PM.

Jam boleh menjadi nombor dari 0 hingga 12. Untuk mendapatkannya, kami menggunakan atau membina ([0-9] | 1 [0-2]), di mana:

• [0-9] sepadan dengan nombor dari 0 hingga 9
• 1 [0-2] sepadan dengan nombor dari 10 hingga 12

Minit [0-5] \ d adalah nombor dari 00 hingga 59.

Kedua (: [0-5] \ d)? Adakah juga nombor dari 00 hingga 59. Kuantifier bermaksud sifar atau satu kejadian sejak saat mungkin atau mungkin tidak dimasukkan dalam nilai masa.

Ungkapan 2 : \ b ([0-9] | [0-1] \ d | 2 [0-3]): [0-5] \ d (: [0-5] \ d)? (?! :)

Mengekstrak kali tanpa am/pm.

Bahagian jam boleh menjadi nombor dari 0 hingga 32. Untuk mendapatkannya, berbeza atau membina ([0-9] | [0-1] \ d | 2 [0-3]) digunakan, di mana:

• [0-9] sepadan dengan nombor dari 0 hingga 9
• [0-1] \ d sepadan dengan nombor dari 00 hingga 19
• 2 [0-3] sepadan dengan nombor dari 20 hingga 23

Bahagian minit dan kedua adalah sama seperti dalam ungkapan 1 di atas.

Lookahead negatif (?! :) ditambah untuk melangkau rentetan seperti 20:30:80.

Oleh kerana PM/AM boleh menjadi huruf besar atau huruf kecil, kami membuat fungsi kes-tidak sensitif:

=RegExpExtract(A5, $A$2, 1, FALSE)

Mudah -mudahan, contoh -contoh di atas memberi anda beberapa idea tentang cara menggunakan ungkapan biasa dalam lembaran kerja Excel anda. Malangnya, tidak semua ciri ekspresi biasa klasik disokong dalam VBA. Sekiranya tugas anda tidak dapat dicapai dengan VBA Regexp, saya menggalakkan anda membaca bahagian seterusnya yang membincangkan fungsi .NET Regex yang lebih kuat.

Fungsi regex berasaskan .net adat untuk mengekstrak teks dalam excel

Tidak seperti fungsi VBA RegEXP yang boleh ditulis oleh mana -mana pengguna Excel, .NET Regex adalah dunia pemaju. Rangka kerja Microsoft .NET menyokong sintaks ekspresi biasa yang disesuaikan dengan Perl 5. Artikel ini tidak akan mengajar anda bagaimana menulis fungsi tersebut (saya bukan seorang pengaturcara dan tidak mempunyai idea yang sedikit tentang bagaimana untuk melakukannya :)

Empat fungsi kuat yang diproses oleh enjin .NET Regex standard telah ditulis oleh pemaju kami dan termasuk dalam Suite Ultimate. Di bawah ini, kami akan menunjukkan beberapa kegunaan praktikal fungsi yang direka khas untuk mengekstrak teks dalam Excel.

Petua. Untuk maklumat mengenai sintaks .NET Regex, sila rujuk bahasa ekspresi biasa .NET.

Cara Mengekstrak String dalam Excel Menggunakan Ekspresi Biasa

Dengan mengandaikan anda mempunyai versi terkini Suite Ultimate yang dipasang, mengekstrak teks menggunakan ekspresi biasa beralih ke dua langkah ini:

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

   

2. Pada anak tetingkap Regex Tools , pilih data sumber, masukkan corak regex anda, dan pilih pilihan Ekstrak . Untuk mendapatkan hasilnya sebagai fungsi tersuai, bukan nilai, pilih sisipan sebagai kotak semak formula . Apabila selesai, klik butang Ekstrak .

   

Hasilnya akan muncul dalam lajur baru di sebelah kanan data asal anda:

Sintaks yang boleh dibebankan

Fungsi tersuai kami mempunyai sintaks berikut:

BolehBitsRegexExtract (rujukan, biasa_expression)

Di mana:

• Rujukan (diperlukan) - Rujukan kepada sel yang mengandungi rentetan sumber.
• Regular_expression (diperlukan) - Corak regex untuk dipadankan.

Nota penting! Fungsi ini hanya berfungsi pada mesin dengan Suite Ultimate untuk dipasang Excel.

Nota penggunaan

Untuk menjadikan keluk pembelajaran anda lebih lancar dan pengalaman anda lebih menyeronokkan, sila perhatikan perkara -perkara ini:

1. Untuk membuat formula, anda boleh menggunakan Alat Regex kami, atau dialog Fungsi Sisipan Excel, atau taipkan nama fungsi penuh dalam sel. Sebaik sahaja formula dimasukkan, anda boleh menguruskannya (edit, salin atau bergerak) seperti mana -mana formula asli.
2. Corak yang anda masukkan pada panel Regex Tools pergi ke hujah 2 ^ND . Ia juga mungkin untuk menyimpan ungkapan biasa dalam sel yang berasingan. Dalam kes ini, hanya gunakan rujukan sel untuk hujah 2 ^ND .
3. Fungsi ini mengekstrak perlawanan pertama yang dijumpai .
4. Secara lalai, fungsi itu sensitif kes . Untuk pencocokan kes yang tidak sensitif, gunakan corak (? I).
5. Jika perlawanan tidak dijumpai, ralat #N/A dikembalikan.

Regex untuk mengekstrak rentetan antara dua aksara

Untuk mendapatkan teks antara dua aksara, anda boleh menggunakan sama ada kumpulan penangkapan atau look-arounds.

Katakan anda ingin mengekstrak teks antara kurungan. Kumpulan penangkapan adalah cara paling mudah.

Corak 1 : \ [(.*?) \]

Dengan penampilan positif dan lookahead, hasilnya akan sama.

Corak 2 : (?

Sila perhatikan bahawa kumpulan penangkapan kami (.*?) Melakukan pencarian malas untuk teks antara dua kurungan - dari yang pertama [ke yang pertama]. Kumpulan penangkapan tanpa tanda tanya (.*) Akan melakukan pencarian tamak dan menangkap segala -galanya dari yang pertama [hingga terakhir].

Dengan corak dalam A2, formula berjalan seperti berikut:

=AblebitsRegexExtract(A5, $A$2)

Cara Mendapatkan Semua Perlawanan

Seperti yang telah disebutkan, fungsi AbleBitsRegexExtract hanya boleh mengekstrak satu perlawanan. Untuk mendapatkan semua perlawanan, anda boleh menggunakan fungsi VBA yang telah dibincangkan sebelumnya. Walau bagaimanapun, terdapat satu kaveat - VBA Regexp tidak menyokong kumpulan menangkap, jadi corak di atas akan mengembalikan aksara "sempadan" juga, kurungan dalam kes kami.

=TEXTJOIN(" ", TRUE, RegExpExtract(A5, $A$2))

Untuk menghilangkan kurungan, ganti mereka dengan rentetan kosong ("") menggunakan formula ini:

=SUBSTITUTE(SUBSTITUTE(TEXTJOIN(", ", TRUE, RegExpExtract(A5, $A$2)), "]", ""),"[","")

Untuk kebolehbacaan yang lebih baik, kami menggunakan koma untuk pematuhan.

Regex untuk mengekstrak teks antara dua tali

Pendekatan yang kami lakukan untuk menarik teks antara dua aksara juga akan berfungsi untuk mengekstrak teks antara dua rentetan.

Sebagai contoh, untuk mendapatkan segala -galanya antara "Ujian 1" dan "Ujian 2", gunakan ungkapan biasa berikut.

Corak : Ujian 1 (.*?) Ujian 2

Formula lengkap ialah:

=AblebitsRegexExtract(A5, "test 1(.*?)test 2")

Regex untuk mengekstrak domain dari URL

Walaupun dengan ungkapan biasa, mengekstrak nama domain dari URL bukan tugas remeh. Elemen utama yang melakukan silap mata adalah kumpulan yang tidak menangkap. Bergantung pada matlamat utama anda, pilih salah satu daripada regex di bawah.

Untuk mendapatkan nama domain penuh termasuk subdomain

Corak : (?: https? \: |

Untuk mendapatkan domain peringkat kedua tanpa subdomain

Corak : (?: https? \: |

Sekarang, mari kita lihat bagaimana ekspresi biasa ini berfungsi dengan contoh "https://www.mobile.ablebits.com" sebagai URL sampel:

• (?: https? \: |^| \ s) - Kumpulan bukan penapisan. Ia sepadan tetapi tidak menangkap substring yang didahului oleh salah satu daripada yang berikut: https, http, permulaan rentetan (^), watak ruang putih (\ s). Dua item terakhir dimasukkan untuk mengendalikan URL-relatif protokol seperti "//google.com".
• \/ \/ ​​- Dua slash ke hadapan (masing -masing didahului oleh backslash untuk melarikan diri dari makna khas slash ke hadapan dan mentafsirkannya secara harfiah).
• (?: [a-za-z \ d \-\.] {2,255} \.)? -Kumpulan bukan penangkap untuk mengenal pasti peringkat ketiga, peringkat keempat, dan lain-lain. Domain, jika ada ( mudah alih dalam URL sampel kami). Dalam corak pertama, ia diletakkan dalam kumpulan penangkapan yang lebih besar untuk mendapatkan semua subdomain tersebut dimasukkan ke dalam pengekstrakan. Subdomain boleh dari 2 hingga 255 aksara panjang, oleh itu {2,255} kuantifier.
• ([A-za-z \ d \-] {1,63} \. [A-za-z] {2,24})-Menangkap kumpulan untuk mengekstrak domain peringkat kedua ( mampu ) dan domain peringkat atas ( com ). Panjang maksimum domain peringkat kedua ialah 63 aksara. Domain peringkat atas terpanjang yang kini wujud mengandungi 24 aksara.

Bergantung pada ekspresi biasa yang dimasukkan dalam A2, formula di bawah akan menghasilkan hasil yang berbeza:

=AblebitsRegexExtract(A5, $A$2)

Regex untuk mengekstrak nama domain penuh dengan semua subdomain:

Regex untuk mengekstrak domain peringkat kedua tanpa subdomain:

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

Muat turun yang ada

Excel Regex Extract Contoh (fail .xlsm) Versi percubaan suite muktamad (.exe file)

Atas ialah kandungan terperinci Regex untuk mengekstrak rentetan dalam Excel (satu atau semua perlawanan). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!