Regex untuk membuang watak atau teks tertentu dalam excel

Pernahkah anda fikir betapa kuatnya Excel jika seseorang dapat memperkayakan kotak alatnya dengan ekspresi biasa? Kami bukan sahaja berfikir tetapi bekerja di atasnya :) Dan sekarang, anda boleh menambah fungsi regex yang indah ini untuk buku kerja anda sendiri dan menghapuskan substrings yang sepadan dengan corak dalam masa yang singkat!

Minggu lalu, kami melihat cara menggunakan ungkapan biasa untuk menggantikan rentetan dalam Excel. Untuk ini, kami mencipta fungsi penggantian regex tersuai. Seperti yang ternyata, fungsi melampaui penggunaan utamanya dan bukan sahaja boleh menggantikan rentetan tetapi juga mengeluarkannya. Bagaimana mungkin? Dari segi Excel, mengeluarkan nilai tidak lain tetapi menggantikannya dengan rentetan kosong, sesuatu yang fungsi regex kami sangat baik!

• Fungsi VBA Regexp untuk membuang substrings
• Excel Regex Keluarkan contoh
  • Padam semua perlawanan atau perlawanan tertentu
  • Jalur watak tertentu
  • Keluarkan watak khas
  • Padam aksara bukan angka
  • Lepaskan segala -galanya selepas ruang
  • Keluarkan teks selepas watak tertentu
  • Membersihkan semuanya sebelum ruang
  • Hilangkan segalanya sebelum watak
  • Keluarkan segala -galanya kecuali
  • Ungkapan biasa untuk mengeluarkan tag html
• Dapat membuang regex alat

Fungsi VBA Regexp Untuk Mengalihkan Substrings Dalam Excel

Seperti yang kita ketahui, ungkapan biasa tidak disokong dalam Excel secara lalai. Untuk membolehkan mereka, anda perlu membuat fungsi yang ditentukan oleh pengguna anda sendiri. Berita baiknya ialah fungsi sedemikian telah ditulis, diuji, dan bersedia untuk digunakan. Apa yang anda perlu lakukan ialah menyalin kod ini, tampalnya dalam editor VBA anda, dan kemudian simpan fail anda sebagai buku kerja yang dibolehkan makro (.xlsm).

Fungsi ini mempunyai sintaks berikut:

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

Tiga hujah pertama diperlukan, dua yang terakhir adalah pilihan.

Di mana:

• Teks - Rentetan teks untuk dicari.
• Corak - Ekspresi biasa untuk mencari.
• Penggantian - Teks untuk diganti dengan. Untuk mengeluarkan substring yang sepadan dengan corak, gunakan rentetan kosong ("") untuk penggantian.
• Instance_num (pilihan) - Contoh untuk menggantikan. Jika ditinggalkan, semua perlawanan yang dijumpai diganti (lalai).
• Match_case (pilihan) - Nilai boolean yang menunjukkan sama ada untuk memadankan atau mengabaikan kes teks. Untuk padanan sensitif kes, gunakan benar (lalai); Untuk kes -insensitif - palsu.

Untuk maklumat lanjut, sila lihat fungsi RegexPreplace.

Petua. Dalam kes yang mudah, anda boleh mengeluarkan watak atau kata -kata tertentu dari sel dengan formula Excel. Tetapi ungkapan biasa menyediakan lebih banyak pilihan untuk ini.

Cara Menghapus Rentetan Menggunakan Ekspresi Biasa - Contoh

Seperti yang disebutkan di atas, untuk menghapuskan bahagian teks yang sepadan dengan corak, anda harus menggantikannya dengan rentetan kosong. Jadi, formula generik mengambil bentuk ini:

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

Contoh di bawah menunjukkan pelbagai pelaksanaan konsep asas ini.

Keluarkan semua perlawanan atau perlawanan tertentu

Fungsi RegexPreplace direka untuk mencari semua substring yang sepadan dengan regex yang diberikan. Yang kejadian untuk dikeluarkan dikawal oleh hujah pilihan ^ke -4, bernama Instance_num .

Lalai adalah "semua perlawanan" - apabila argumen Instance_num ditinggalkan, semua perlawanan yang dijumpai dikeluarkan. Untuk memadam perlawanan tertentu, tentukan nombor contoh.

Dalam rentetan di bawah, katakan anda ingin memadamkan nombor pesanan pertama. Semua nombor sedemikian bermula dengan tanda hash (#) dan mengandungi 5 digit yang tepat. Jadi, kita dapat mengenal pasti mereka menggunakan regex ini:

Corak : #\ d {5} \ b

Kata sempadan \ b menentukan bahawa substring yang sepadan tidak boleh menjadi sebahagian daripada rentetan yang lebih besar seperti #10000001.

Untuk membuang semua perlawanan, argumen Instance_num tidak ditakrifkan:

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

Untuk membasmi hanya kejadian pertama, kami menetapkan hujah Instance_num kepada 1:

=RegExpReplace(A5, "#\d{5}\b", "", 1)

Regex untuk membuang watak tertentu

Untuk melepaskan watak -watak tertentu dari rentetan, tuliskan semua watak yang tidak diingini dan memisahkannya dengan bar menegak | yang bertindak sebagai pengendali atau pengendali dalam regexes.

Sebagai contoh, untuk menyeragamkan nombor telefon yang ditulis dalam pelbagai format, pertama kita menyingkirkan watak -watak tertentu seperti kurungan, tanda hubung, titik dan ruang putih.

Corak : \ (| \) |-| \. | \ S

=RegExpReplace(A5, "\(|\)|-|\.|\s", "")

Hasil operasi ini adalah nombor 10 digit seperti "1234567890".

Untuk kemudahan, anda boleh memasukkan regex adalah sel yang berasingan, dan merujuk kepada sel itu menggunakan rujukan mutlak seperti $ A $ 2:

=RegExpReplace(A5, $A$2, "")

Dan kemudian, anda boleh menyeragamkan pemformatan cara yang anda inginkan dengan menggunakan operator concatenation (&) dan fungsi teks seperti kanan, pertengahan dan kiri.

Sebagai contoh, untuk menulis semua nombor telefon dalam format (123) 456-7890, formula adalah:

="("&LEFT(B5, 3)&") "&MID(B5, 4, 3)&"-"&RIGHT(B5, 4)

Di mana B5 adalah output fungsi regexpreplace.

Keluarkan watak khas menggunakan regex

Dalam salah satu tutorial kami, kami melihat cara menghapuskan watak -watak yang tidak diingini dalam Excel menggunakan fungsi terbina dan tersuai. Ekspresi biasa membuat perkara lebih mudah! Daripada menyenaraikan semua watak untuk memadam, tentukan yang anda mahu simpan :)

Corak ini didasarkan pada kelas watak yang ditolak - sebuah karet dimasukkan ke dalam kelas watak [^] untuk memadankan mana -mana watak tunggal yang tidak dalam kurungan. Kuantum memaksa ia untuk menganggap watak berturut -turut sebagai satu perlawanan, supaya penggantian dilakukan untuk substring yang sepadan dan bukannya untuk setiap watak individu.

Bergantung pada keperluan anda, pilih salah satu regex berikut.

Untuk membuang watak bukan alphanumerik , iaitu semua watak kecuali huruf dan digit:

Corak : [^0-9a-za-z]

Untuk membersihkan semua watak kecuali huruf , digit dan ruang :

Corak : [^0-9a-za-z]

Untuk memadam semua aksara kecuali huruf , digit dan garis bawah , anda boleh menggunakan \ w yang bermaksud mana -mana watak yang bukan watak alfanumerik atau garis bawah:

Corak : \ w

Jika anda ingin menyimpan beberapa watak lain , misalnya tanda baca, letakkannya di dalam kurungan.

Sebagai contoh, untuk melepaskan mana -mana watak selain daripada huruf, digit, tempoh, koma, atau ruang, gunakan regex berikut:

Corak : [^0-9a-za-z \.,]

Ini berjaya menghilangkan semua watak khas, tetapi ruang kosong tambahan kekal.

Untuk membetulkannya, anda boleh sarang fungsi di atas ke yang lain yang menggantikan pelbagai ruang dengan watak ruang tunggal.

=RegExpReplace(RegExpReplace(A5,$A$2,""), " ", " ")

Atau hanya gunakan fungsi trim asli dengan kesan yang sama:

=TRIM(RegExpReplace(A5, $A$2, ""))

Regex untuk membuang watak bukan angka

Untuk memadam semua aksara bukan numerik dari rentetan, anda boleh menggunakan formula panjang ini atau salah satu regex yang sangat mudah yang disenaraikan di bawah.

Padankan mana -mana watak yang bukan digit:

Corak : \ d

Jalur watak bukan angka menggunakan kelas yang ditolak:

Corak : [^0-9]

Corak : [^\ d]

Petua. Jika matlamat anda adalah untuk menghapuskan teks dan tumpahkan nombor yang tersisa ke dalam sel berasingan atau letakkannya dalam satu sel yang dipisahkan dengan pembatas yang ditentukan, kemudian gunakan fungsi RegexPExtract seperti yang dijelaskan dalam cara mengekstrak nombor dari rentetan menggunakan ungkapan biasa.

Regex untuk mengeluarkan semuanya selepas ruang

Untuk menghapuskan segala -galanya selepas ruang, gunakan sama ada ruang () atau Whitespace (\ s) watak untuk mencari ruang pertama dan.* Untuk memadankan mana -mana aksara selepas itu.

Sekiranya anda mempunyai rentetan satu baris yang hanya mengandungi ruang normal (nilai 32 dalam sistem ASCII 7-bit), ia tidak semestinya penting di bawah regex yang anda gunakan. Sekiranya rentetan pelbagai baris, ia membuat perbezaan.

Untuk mengeluarkan segala -galanya selepas watak ruang , gunakan regex ini:

Corak : ".*"

=RegExpReplace(A5, " .*", "")

Formula ini akan melepaskan apa -apa selepas ruang pertama dalam setiap baris . Untuk hasilnya dipaparkan dengan betul, pastikan anda menghidupkan teks bungkus.

Untuk melepaskan segala -galanya selepas ruang putih (termasuk ruang, tab, pulangan kereta dan garis baru), regex adalah:

Corak : \ s.*

=RegExpReplace(A5, "\s.*", "")

Kerana \ s sepadan dengan beberapa jenis ruang putih yang berbeza termasuk garis baru (\ n), formula ini memadamkan segala -galanya selepas ruang pertama dalam sel, tidak kira berapa banyak baris yang ada di dalamnya.

Regex untuk mengeluarkan teks selepas watak tertentu

Menggunakan kaedah dari contoh sebelumnya, anda boleh membasmi teks selepas mana -mana watak yang anda tentukan.

Untuk mengendalikan setiap baris secara berasingan:

Corak Generik : Char.*

Dalam rentetan satu baris, ini akan menghilangkan segala-galanya selepas char . Dalam rentetan berbilang baris, setiap baris akan diproses secara individu kerana dalam rasa VBA regex, tempoh (.) Memadankan mana-mana watak kecuali garis baru.

Untuk memproses semua baris sebagai rentetan tunggal:

Corak generik : char (. | \ N)*

Untuk memadam apa -apa selepas watak tertentu, termasuk baris baru, \ n ditambah kepada corak.

Sebagai contoh, untuk mengeluarkan teks selepas koma pertama dalam rentetan, cuba ungkapan biasa ini:

Corak :,.*

Corak :, (. | \ N)*

Dalam tangkapan skrin di bawah, anda boleh mengkaji bagaimana hasilnya berbeza.

Regex untuk mengeluarkan semuanya sebelum ruang

Apabila bekerja dengan rentetan teks yang panjang, anda kadang -kadang mahu menjadikannya lebih pendek dengan mengeluarkan bahagian maklumat yang sama di semua sel. Di bawah ini kita akan membincangkan dua kes tersebut.

Keluarkan semuanya sebelum ruang terakhir

Seperti contoh sebelumnya, ungkapan biasa bergantung kepada pemahaman anda tentang "ruang".

Untuk memadankan apa -apa sehingga ruang terakhir , regex ini akan dilakukan (tanda petikan ditambah untuk membuat ruang selepas asterisk ketara).

Corak : ".*"

Untuk memadankan apa -apa sebelum ruang kosong terakhir (termasuk ruang, tab, pulangan kereta, dan garis baru), gunakan ungkapan biasa ini.

Corak :.*\ S

Perbezaannya amat ketara pada rentetan berbilang baris.

Jalur segalanya sebelum ruang pertama

Untuk memadankan apa -apa sehingga ruang pertama dalam rentetan, anda boleh menggunakan ungkapan biasa ini:

Corak : ^ [ ^]*

Dari permulaan rentetan ^, kita sepadan dengan sifar atau lebih aksara bukan ruang [ ^]* yang segera diikuti oleh satu atau lebih ruang "". Bahagian terakhir ditambah untuk mencegah potensi ruang utama dalam hasilnya.

Untuk mengalih keluar teks sebelum ruang pertama dalam setiap baris, formula ditulis dalam mod lalai "semua perlawanan" ( instance_num ditinggalkan):

=RegExpReplace(A5, "^[^ ]* ", "")

Untuk memadam teks sebelum ruang pertama di baris pertama, dan biarkan semua baris lain utuh, argumen Instance_num ditetapkan kepada 1:

=RegExpReplace(A5, "^[^ ]* ", "", 1)

Regex untuk melepaskan semuanya sebelum watak

Cara paling mudah untuk menghapuskan semua teks sebelum watak tertentu adalah dengan menggunakan regex seperti ini:

Corak Generik : ^[ ^char]*char

Diterjemahkan ke dalam bahasa manusia, ia berkata: "Dari permulaan rentetan berlabuh oleh ^, perlawanan 0 atau lebih aksara kecuali char [ ^char]* sehingga kejadian pertama char .

Sebagai contoh, untuk memadam semua teks sebelum kolon pertama, gunakan ungkapan biasa ini:

Corak : ^[ ^:]*:

Untuk mengelakkan ruang utama dalam hasil, tambahkan aksara ruang putih \ s* hingga akhir. Ini akan menghilangkan segala -galanya sebelum kolon pertama dan memotong mana -mana ruang selepas itu:

Corak : ^[ ^:]*: \ s*

=RegExpReplace(A5, "^[^:]*:\s*", "")

Petua. Selain ungkapan biasa, Excel mempunyai cara tersendiri untuk mengeluarkan teks mengikut kedudukan atau perlawanan. Untuk mengetahui bagaimana untuk mencapai tugas dengan formula asli, sila lihat cara membuang teks sebelum atau selepas watak dalam Excel.

Regex untuk mengeluarkan semuanya kecuali

Untuk membasmi semua aksara dari rentetan kecuali yang anda mahu simpan, gunakan kelas watak yang ditolak.

Sebagai contoh, untuk menghapuskan semua aksara kecuali huruf kecil dan titik, regex adalah:

Corak : [^az \.]

Sebenarnya, kita boleh lakukan tanpa pengkuantum di sini kerana fungsi kami menggantikan semua perlawanan yang dijumpai. Kuantum hanya menjadikannya sedikit lebih cepat - bukannya mengendalikan setiap watak individu, anda menggantikan substring.

=RegExpReplace(A5, "[^az\.] ", "")

Regex untuk mengeluarkan tag html dalam excel

Mula -mula, perlu diperhatikan bahawa HTML bukan bahasa biasa, jadi menghuraikannya menggunakan ungkapan biasa bukanlah cara terbaik. Yang berkata, Regexes pasti dapat membantu tag jalur keluar dari sel anda untuk membuat pembersih dataset anda.

Memandangkan tag HTML sentiasa diletakkan dalam kurungan sudut , anda boleh mencari mereka menggunakan salah satu regex berikut.

Kelas yang ditolak:

Corak : ]*>

Di sini, kita sepadan dengan pendakap sudut pembukaan, diikuti oleh sifar atau lebih banyak kejadian mana -mana watak kecuali pendakap sudut penutup [^>]* sehingga pendakap sudut penutup terdekat.

Pencarian malas:

Corak : <.>

Di sini, kami sepadan dengan apa -apa dari pendakap pembukaan pertama ke pendakap penutupan pertama. Tanda tanya kuasa.* Untuk dipadankan dengan beberapa aksara yang mungkin sehingga ia mendapati pendakap penutupan.

Mana -mana corak yang anda pilih, hasilnya akan sama.

Sebagai contoh, untuk mengeluarkan semua tag HTML dari rentetan dalam A5 dan meninggalkan teks, formula adalah:

=RegExpReplace(A5, "]*>", "")

Atau anda boleh menggunakan penguantuman malas seperti yang ditunjukkan dalam tangkapan skrin:

Penyelesaian ini berfungsi dengan sempurna untuk teks tunggal (baris 5 - 9). Untuk pelbagai teks (baris 10 - 12), hasilnya dipersoalkan - teks dari tag yang berbeza digabungkan menjadi satu. Adakah ini betul atau tidak? Saya takut, ini bukan sesuatu yang dapat diputuskan dengan mudah - semuanya bergantung pada pemahaman anda tentang hasil yang diinginkan. Sebagai contoh, dalam B11, hasil "A1" dijangka; Semasa di B10, anda mungkin mahu "Data1" dan "Data2" dipisahkan dengan ruang.

Untuk mengeluarkan tag HTML dan memisahkan teks yang tinggal dengan ruang, anda boleh meneruskan dengan cara ini:

1. Gantikan tag dengan ruang "", bukan rentetan kosong:

   =RegExpReplace(A5, "]*>", " ")

2. Kurangkan pelbagai ruang ke watak ruang tunggal:

   =RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " ")

3. Potong ruang terkemuka dan trailing:

   =TRIM(RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " "))

Hasilnya akan kelihatan seperti ini:

Dapat membuang regex alat

Jika anda mempunyai peluang untuk menggunakan Suite Ultimate kami untuk Excel, anda mungkin sudah menemui alat Regex baru yang diperkenalkan dengan pelepasan baru -baru ini. Keindahan fungsi regex berasaskan .NET ini adalah bahawa mereka, pertama, menyokong sintaks ekspresi biasa yang lengkap secara bebas daripada batasan VBA RegEXP, dan kedua, tidak memerlukan memasukkan sebarang kod VBA dalam buku kerja anda kerana semua integrasi kod dilakukan oleh kami di backend.

Bahagian kerja anda adalah untuk membina ungkapan biasa dan berkhidmat kepada fungsi :) Izinkan saya menunjukkan kepada anda bagaimana untuk melakukannya dengan contoh praktikal.

Cara mengeluarkan teks dalam kurungan dan kurungan menggunakan regex

Dalam rentetan teks yang panjang, maklumat yang kurang penting sering disertakan dalam [kurungan] dan (kurungan). Bagaimanakah anda membuang butiran yang tidak relevan dengan menyimpan semua data lain?

Malah, kami telah membina regex yang sama untuk memadam tag HTML, iaitu teks dalam kurungan sudut. Jelas sekali, kaedah yang sama akan berfungsi untuk kurungan persegi dan bulat juga.

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

Caranya menggunakan kuantifi malas (*?) Untuk memadankan substring yang paling singkat. Kumpulan pertama (\ (.*? \)) Sepadan dengan apa -apa dari kurungan pembukaan kepada kurungan penutup pertama. Kumpulan kedua (\ [.*? \]) Sepadan dengan apa -apa dari pendakap pembukaan ke pendakap penutupan pertama. Bar menegak | bertindak sebagai pengendali atau pengendali.

Dengan corak yang ditentukan, mari kita "memberi makan" kepada fungsi keluarkan regex kami. Inilah Caranya:

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

   

2. Pada anak tetingkap Regex Tools , pilih rentetan sumber anda, masukkan regex anda, pilih pilihan Buang , dan tekan Buang .

   Untuk mendapatkan hasilnya sebagai formula, bukan nilai, pilih sisipan sebagai kotak semak formula .

   Untuk mengeluarkan teks dalam kurungan dari rentetan dalam A2: A5, kami mengkonfigurasi tetapan seperti berikut:

   

Hasilnya, fungsi ABEbitsRegexRemove dimasukkan dalam lajur baru di sebelah data asal anda.

Fungsi ini juga boleh dimasukkan secara langsung ke dalam sel melalui kotak dialog Fungsi Masukkan Standard, di mana ia dikategorikan di bawah mampu .

Oleh kerana cabebitsregexremove direka untuk mengeluarkan teks, ia hanya memerlukan dua argumen - rentetan sumber dan regex. Kedua -dua parameter boleh ditakrifkan secara langsung dalam formula atau dibekalkan dalam bentuk rujukan sel. Sekiranya diperlukan, fungsi tersuai ini boleh digunakan bersama -sama dengan mana -mana orang asli.

Sebagai contoh, untuk memangkas ruang tambahan dalam rentetan yang dihasilkan, anda boleh menggunakan fungsi trim sebagai pembungkus:

=TRIM(AblebitsRegexRemove(A5, $A$2))

Itulah cara untuk mengeluarkan rentetan 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

Keluarkan rentetan menggunakan regex - contoh (.xlsm file) Ultimate Suite - Versi Percubaan (.exe File)

Atas ialah kandungan terperinci Regex untuk membuang watak atau teks tertentu dalam excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!