Cara Menggunakan Fungsi Regex Excel untuk Memperkasakan Carian Anda

Menapis dan mencari dalam Excel bukanlah satu tugas yang mudah. Fungsi regex akan mengubahnya. Kini anda boleh menentukan dengan tepat apa yang anda perlukan—corak kompleks, padanan separa atau pengekstrakan data berstruktur—tanpa usaha.

Jadual Kandungan

• Apa itu Regex?
• Cari corak
• Data tambahan menggunakan Regex
• Cari dan Gantikan dengan Regex
• Menggabungkan Regex dengan fungsi lain

Apa itu Regex?

Regex ialah sejenis corak yang digunakan untuk mencari teks atau rentetan aksara untuk padanan. Pernahkah anda terfikir bagaimana tapak web boleh memberitahu anda templat e-mel yang anda masukkan pada halaman log masuk adalah tidak sah? Berikut ialah contoh corak regex menggunakan tandatangan e-mel dalam tindakan.

Ungkapan biasa bukan unik untuk Excel — ia tersedia dalam banyak editor teks, bahasa pengaturcaraan, alat baris arahan, IDE dan juga pesaing Excel, Helaian Google .

Regex mungkin kelihatan rumit dan ia adalah jika anda ingin menggunakannya sepenuhnya, tetapi anda tidak perlu menjadi seorang pengaturcara untuk menggunakannya dengan berkesan. Dalam sesetengah kes, anda boleh lari dengan hanya mengetahui cara menggunakan beberapa simbol dan corak asas. Panduan ini akan disimpan semudah mungkin supaya anda boleh mula menggunakannya.

Berikut adalah simbol yang akan digunakan dalam panduan ini:

Corak ungkapan biasa mudah yang boleh anda bina menggunakan simbol ini termasuk:

Fungsi regex ialah formula Excel yang dipratentukan yang boleh digunakan untuk menentukan corak untuk mencari dan memanipulasi rentetan teks. Pada masa ini terdapat 3 fungsi regex. Kami akan melihat cara menggunakannya secara berasingan dan dengan fungsi lain.

Cari corak

Fungsi pertama yang akan kita lihat ialah REGEXTEST. Fungsi ini mengambil rentetan teks yang anda ingin gunakan untuk mencari dan corak regex, kemudian menggunakan corak yang kedua untuk mencari padanan dalam corak yang terdahulu. Fungsi akan mengembalikan Benar atau Salah.

Sintaks fungsi REGEXTEST adalah seperti berikut:

REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])

Dua parameter pertama, string_to_search dan regex_pattern_to_use cukup jelas. Parameter [case_sensitivity] adalah pilihan - apa-apa sahaja yang disertakan dalam kurungan segi empat sama apabila bercakap tentang sintaks Excel adalah pilihan - dan menunjukkan sama ada anda mahu carian menjadi sensitif huruf besar dan kecil (0) atau tidak sensitif huruf besar (1). Lalai adalah sensitif huruf besar-besaran.

Contoh akan menggunakan REGEXTEST untuk melihat sama ada pengguna memasukkan alamat e-mel yang sah menggunakan formula berikut:

REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")

Di sini, kami sedang mencari dalam sel B3 untuk melihat sama ada ia mengandungi alamat e-mel menggunakan corak ungkapan biasa di bawah:

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

Jika anda meletakkan formula dalam sel C3 dan masukkan [email protected] dalam sel B3, formula akan mengembalikan True kerana ia sepadan dengan tandatangan e-mel.

Data tambahan menggunakan Regex

Seterusnya, mari kita lihat fungsi REXEXEXTRACT. Fungsi ini mengembalikan subrentetan (sebahagian daripada rentetan) yang sepadan dengan corak regex yang disediakan.

Sintaks fungsi REXEXTRACT adalah seperti berikut:

REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

Meneruskan contoh e-mel, mari tambah formula pada sel B4 untuk mengekstrak nama pengguna bahagian e-mel.

Formula akan kelihatan seperti ini:

=REGEXEXTRACT(B3, "([^@]+)")

Dalam formula ini, kami mengekstrak semua sebelum simbol @ dalam alamat e-mel yang dimasukkan dalam B3.

Cari dan Gantikan dengan Regex

Fungsi regex terakhir yang akan kita lihat ialah REGEXREPLACE. Fungsi ini serupa dengan fungsi REPLACE Excel, tetapi turut menyokong RegEx. Ia memerlukan rentetan teks yang anda ingin ubah suai dan menyemak sama ada mana-mana subrentetan sepadan dengan corak regex yang ditentukan. Jika ditemui, ia menggantikan rentetan itu dengan rentetan gantian yang disediakan.

Sintaks fungsi REGEXREPLACE adalah seperti berikut:

REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])

Berikut ialah parameter penting untuk diperhatikan dalam fungsi ini:

• string_to_modify : Rentetan teks yang anda ingin ubah suai.
• replacement_string : Rentetan untuk menggantikan subrentetan dengan.
• number_of_occurrences : Contoh tepat yang anda ingin gantikan.

Berikut ialah contoh menggunakan fungsi untuk menggantikan bahagian nama pengguna e-mel dengan rentetan teks lain:

=REGEXREPLACE(B3, "^[^@]+", "jane.doe")

Nilai B3 ialah [email protected] dan selepas kami memasukkan formula di atas dalam sel C3, ia akan mengembalikan [email protected].

Menggabungkan Regex dengan fungsi lain

Anda juga boleh menggabungkan fungsi regex dengan fungsi lain dalam Excel. Sebagai contoh, anda boleh menggabungkan fungsi REGEXTEST dengan pernyataan IF Excel dan memaparkan mesej yang sesuai berdasarkan keputusan.

Berikut adalah contoh formula:

=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

Formula ini menggunakan pernyataan IF untuk menyemak sama ada alamat e-mel yang dimasukkan dalam sel B6 adalah sah dan kemudian memaparkan Ini adalah alamat e-mel yang sah! jika BENAR atau Alamat e-mel tidak sah! jika SALAH . Selain itu, anda boleh memasangkan formula ini dengan fungsi FIND untuk mencari data dalam Excel dengan cepat.

Ini adalah cara yang baik untuk mula menggunakan RegEx dalam Excel. Kes penggunaan dan kemungkinan hanya terhad oleh imaginasi anda.