Bagaimana cara menggunakan regexmatch di google sheets?

Menelusuri di dalam tabel adalah tugas umum saat bekerja dengan Google Spreadsheet. Dengan fungsi REGEXMATCH, kita dapat dengan mudah menemukan jawaban atas pertanyaan apakah ada kata yang sama persis dengan kata yang kita cari dalam sebuah tabel

Kami dapat dengan mudah mencari dengan 'Temukan dan ganti'. Anda dapat menemukan informasi terperinci di artikel ini. "Bagaimana cara mencari di google sheets?"

Di sini REGEXMATCH dibagi sebagai berikut. Kita dapat mencari kecocokan dengan kondisi seperti dimulai dengan x, dilanjutkan dengan y, diakhiri dengan z. Sebagai imbalannya, ia mengembalikan BENAR atau SALAH

Sintaksnya agak berantakan tergantung kondisi yang ingin kita cari. Tapi Anda tidak perlu khawatir. Di bawah ini saya akan menulis ekspresi yang akan Anda gunakan saat mencari

Seperti apa sintaks fungsi REGEXMATCH?

Saat kami mengetik kata atau huruf yang ingin kami cari dengan 'Temukan dan ganti', kami dapat mencapai hasil yang kami inginkan. Namun jika kita tidak tahu persis kata yang ingin kita cari, kita mungkin ingin mencari kata yang serupa. Dalam kasus seperti itu, kami menggunakan beberapa karakter untuk kondisi tersebut

Metakarakter

Definisi

^

Dari apa sel itu dimulai

$

Dengan apa sel itu diakhiri

|

Operator 'atau'

[]

Artinya berisi huruf dari a sampai z

[a-z]

Artinya berisi huruf dari A sampai Z

[A-Z]

Artinya berisi angka dari 0 sampai 9

+

Pengulangan karakter

.

Mengacu pada satu karakter

Mari kita periksa dengan contoh

Apakah ini dimulai dengan "filter"?

=REGEXMATCH(A1,”^filter. Filter”) Kita harus menulis keduanya karena peka terhadap huruf besar-kecil. “. ” atau berarti

Jika kita mempertanyakan sesuatu yang tidak dimulai dengan "x", kita harus menggunakan "^"

Apakah diakhiri dengan "Dasbor"?

=REGEXMATCH(A1,” Dasbor$”)

Jika kita menanyakan akhir sel dengan "x", kita harus meletakkan karakter "$" di akhir

Apakah itu diakhiri dengan "dia" atau "dia"?

=REGEXMATCH(A1,” dia. dia$”)

Apakah sel A1 berisi angka?

=REGEXMATCH(A1,”[0-9]”)

Kita juga bisa mengetahui apakah ada angka di dalam sel dengan menggunakan “\d”

Apakah sel berisi alamat email?

Ini bisa kita pelajari dengan mudah. Karena alamat email berisi "@", kita hanya perlu memasukkan "@" di dalam fungsi

Kita dapat memeriksa apakah sel dimulai dengan "x" dan diakhiri dengan "y"

=REGEXMATCH(A1,"^Bagaimana[a-zA-Z0-9 ]+Dasbor$")

Dengan fungsi ini, kita bisa mencari. Mari kita jelaskan satu per satu

A1 → Akan menjadi sel yang ingin saya kueri

^Bagaimana→ Apakah dimulai dengan Bagaimana?

[a-zA-Z]→ Mencakup huruf besar dan kecil yang digunakan antara awal dan akhir

[a-zA-Z0-9 ]→Selain huruf besar dan huruf kecil, kalimat juga bisa berisi angka. Jadi saya menambahkan [0-9 ] untuk memperluas pencarian. Perlu dicatat bahwa ada spasi setelah 9. Karena akan ada ruang di antara kata-kata

Posting ini mengeksplorasi rumus REGEX Google Sheets dengan serangkaian contoh untuk mengilustrasikan cara kerjanya

Ekspresi reguler, atau singkatnya REGEX, adalah alat untuk memecahkan masalah dengan string teks. Mereka bekerja dengan mencocokkan pola

Anda menggunakan REGEX untuk menyelesaikan masalah seperti menemukan nama atau nomor telepon dalam data, memvalidasi alamat email, mengekstraksi URL, mengganti nama file yang berisi kata "Application", dll.

Mereka memiliki reputasi sebagai orang yang keras, tetapi begitu Anda mempelajari beberapa aturan dasar dan memahami cara kerjanya, Anda dapat menggunakannya secara efektif

Ada tiga rumus REGEX Google Sheets. REGEXMATCH, REGEXEXTRACT, dan REGEXREPLACE

Masing-masing memiliki pekerjaan tertentu

• REGEXMATCH akan mengkonfirmasi apakah menemukan pola dalam teks

• REGEXEXTRACT akan mengekstrak teks yang sesuai dengan pola

• REGEXREPLACE akan menggantikan teks yang sesuai dengan pola

Mari kita pahami mereka dengan serangkaian contoh

Daftar isi

Contoh 1. Rumus REGEX Google Sheets REGEXMATCH

Fungsi REGEXMATCH mengembalikan TRUE jika cocok dengan pola yang Anda berikan di manapun dalam teks dan FALSE jika tidak ada kecocokan dalam teks

Misalnya, kita memiliki dataset anggur vintage ini, di mana setiap baris memiliki campuran teks dan/atau angka

Mari buat REGEXMATCH sederhana untuk menguji apakah sel berisi angka, i. e. tahun vintage diberikan

Di sel A2, tambahkan rumus ini

=REGEXMATCH(A2,"[0-9]")

Ini akan memberikan keluaran BENAR jika menemukan angka dalam string, atau SALAH jika tidak ada angka

[0-9]+_

cocok dengan angka apa pun dari 0 hingga 9 dalam string input

Jadi, asalkan ada satu angka di input string, pola ini akan memberi kita kecocokan

Lihat bagaimana teks tanpa angka “House white wine” memberikan keluaran FALSE karena tidak ada kecocokan

Catatan penting

Pola ini cocok dengan satu angka. Tidak peduli apa lagi yang mungkin ada di dalam sel

Misalnya, ia mengembalikan BENAR untuk string tidak berarti yang berisi angka, yang mungkin bukan perilaku yang Anda cari dalam kasus ini

Di akhir tutorial ini, Anda akan cukup mengerti untuk mengetahui cara memperbaikinya sendiri

(Untuk solusinya, lihat. )

Contoh 2. Rumus REGEX Google Sheets REGEXEXTRACT

Dengan menggunakan dataset wine yang sama seperti di atas, kami ingin membuat kolom baru di dataset kami dengan vintages, i. e. kolom dengan tahun saja

Ini adalah contoh sempurna kapan menggunakan rumus REGEX Google Sheets. Kami akan membuat pola ekspresi reguler untuk mencocokkan angka apa pun dan kemudian menggunakan REGEXEXTRACT untuk mengekstraknya

Seperti semua yang ada di spreadsheet, ada beberapa pola REGEX yang bisa menyelesaikan ini

Kami melihat pola ini di atas

[0-9]

tapi kita juga bisa menggunakan kelas karakter bernama untuk digit

\d

Ini cocok dengan digit apa pun (mis. e. angka 0 sampai 9)

Jadi rumus REGEXEXTRACT untuk mengekstrak tahun terlihat seperti ini

=REGEXEXTRACT(A2,"[0-9]+")_

atau

=REGEXEXTRACT(A2,"\d+")

Kemudian

+_

berarti mendapatkan satu atau lebih

Kedua rumus mengembalikan hasil "1999" karena teks di A2 adalah "Red Bordeaux 1999"

Jika tidak ada angka yang ditemukan, rumus mengembalikan kesalahan #N/A

Dua Catatan Penting

Catatan 1

Rumus REGEX membutuhkan input teks dan mereka memberi Anda output teks kembali. Jadi output 1999 di atas diformat sebagai teks. Untuk mengonversi ke angka, Anda perlu membungkus hasilnya dengan fungsi VALUE. Lihat Contoh 4 di bawah ini untuk lebih jelasnya

Catatan 2

Jika ada lebih banyak angka dalam string teks e. g. “Red Bordeaux 1999 or 2001” hanya 1999 yang dikembalikan oleh rumus REGEXEXTRACT karena tidak cocok dengan spasi atau huruf di antara angka

Itu hanya cocok dengan angka, jadi cocok dengan angka pertama yang dilihatnya, lalu terus cocokkan angka hingga mencapai bukan angka pertama di mana ia berhenti cocok, i. e. ruang pada akhir tahun 1999

Contoh 3. Rumus REGEX Google Sheets REGEXREPLACE

REGEXREPLACE akan mengganti semua kumpulan angka dalam teks dengan nilai baru, misalnya rumus ini

=REGEXREPLACE(A2,"\d+","2021")

akan mengganti 1999 dalam kalimat "Red Bordeaux 1999" dengan "2021" dan mengembalikan jawabannya. Merah Bordeaux 2021

Catatan penting

Fungsi REGEXREPLACE menggantikan SEMUA set angka dalam teks, tidak seperti REGEXEXTRACT yang hanya mengekstrak pola pertama yang cocok

Contoh 4. Gunakan REGEXEXTRACT Dan VALUE Untuk Mengekstrak Angka Dari Teks

Pertimbangkan data ini tentang kapal supertanker

Apakah mungkin untuk mengekstrak angka-angka itu dengan rumus REGEX, meskipun formatnya berbeda?

Anda bertaruh

Rumus ini akan mengekstrak angka dengan atau tanpa pemisah ribuan dan/atau tempat desimal

=REGEXEXTRACT(A2,"[\d,.]+")_

Pola REGEX

[\d,.]+_

berarti cocok dengan angka, koma, atau titik apa pun dan mengekstraknya

Jadi rumus REGEXEXTRACT cocok dengan digit pertama yang ditemukan kemudian melanjutkan pencocokan asalkan karakter berikutnya adalah digit lain atau koma atau titik, Ketika mencapai sesuatu yang lain, seperti spasi atau huruf, kecocokan akan terputus, dan

Kami belum selesai

Menggunakan Fungsi VALUE Untuk Mengubah Keluaran Menjadi Angka

Output dari rumus REGEXEXTRACT adalah string, bukan angka

Jadi kita perlu mengonversi output menjadi angka dengan membungkus hasilnya dengan fungsi VALUE seperti ini

[0-9]+_0

Namun, rumus di atas tidak mudah

Meningkatkan Pencocokan Pola

Jika string teks memiliki titik atau koma sebelum digit pertama maka ini akan diekstraksi sebagai kecocokan

Misalnya, jika string teks input adalah. “Kapalnya sangat besar. Ini muatan penuh 630.962 ton. ” maka rumus REGEXEXTRACT dari atas akan mengekstrak periode pertama saja

Ini cocok dengan periode setelah "besar" dan kemudian menghentikan pertandingan karena karakter spasi yang mengikutinya

Bagaimana kita mengubah rumus untuk memastikan ekstrak dimulai dengan angka?

Nah, kita ubah REGEX untuk mencocokkan angka terlebih dahulu, sebelum yang lainnya, seperti ini

[0-9]+_1

Di sini REGEX mencocokkan satu digit terlebih dahulu, sebelum mencari lebih banyak digit, koma, atau titik

Jika Anda bermata elang, Anda akan melihat bahwa plus + telah berubah menjadi bintang [0-9]+3 after the square bracket. This means zero or more of the characters in the square brackets, to account for a situation where there is a single-digit number that we want to match.

Pola REGEX

[0-9]+_4

cocok dengan satu digit, diikuti dengan nol atau lebih karakter yang merupakan digit, koma, atau titik

Sekarang hasil ekstraksi rumus adalah 630.962, yang merupakan jawaban yang benar

Ingat, output dari rumus REGEXEXTRACT adalah string, jadi Anda harus membungkusnya dengan fungsi VALUE untuk mengonversinya menjadi angka, mis. g

[0-9]+_5

Contoh 5. Periksa Nomor Telepon Dengan REGEXMATCH

Untuk contoh ini, saya akan mempertimbangkan nomor telepon AS dengan tanda hubung di antara bagian-bagiannya, mis. e. angka format ini. XXX-XXX-XXXX

Itu 3 digit, lalu tanda hubung, 3 digit, tanda hubung, lalu 4 digit

Di akhir tutorial ini, Anda akan memiliki informasi yang cukup untuk mengubah contoh ke wilayah lain di dunia

Ini datanya

Mari buat rumus REGEX untuk memeriksa apakah string di kolom A adalah nomor telepon AS yang valid

Menggunakan apa yang kita pelajari di atas, kita tahu ekspresi itu

\d

angka yang cocok. Jadi upaya pertama kami adalah formula ini

Langkah 1

[0-9]+_7

Ini cocok dengan 3 digit, lalu tanda hubung, 3 digit, tanda hubung, lalu 4 digit dan berfungsi dengan baik. Itu menunjukkan BENAR ketika cocok dengan nomor telepon dan SALAH sebaliknya

Tapi itu bertele-tele. Kita dapat menyederhanakannya dengan menggunakan klausa quantifier

Langkah 2

[0-9]+_8

[0-9]+9 artinya sama persis dengan 3 pola sebelumnya, i. e. cocok tepat 3 digit.

Ini berfungsi dengan baik, kecuali masih cocok dengan angka terakhir di baris 8. Itu cocok dengan 3-3-4 dan tidak peduli dengan angka tambahan yang muncul di akhir

Tapi kami tahu ini bukan nomor telepon yang valid, jadi bagaimana cara menghentikannya setelah 4 digit dan mendiskon apa pun dengan lebih dari 4 digit di set terakhir?

Batas Kata

Kami membungkus ekspresi dengan ekspresi khusus yang disebut batas kata, dilambangkan dengan

[0-9]0

sebelum dan sesudah ekspresi utama

Secara teknis, ini cocok dengan "kosong dengan lebar nol". Apa artinya itu menandai batas antara karakter kata (mis. g. huruf atau angka atau _) dan karakter bukan kata. Jadi itu akan mencocokkan angka hingga 4 dan kemudian mencari batas. Digit kelima akan memutuskan pertandingan, tetapi spasi tidak akan membatalkannya karena menentukan batas

Ekspresi baru kita terlihat seperti ini, dengan \b di awal dan akhir

Langkah 3

[0-9]1

Hal terakhir yang mungkin kita lakukan adalah membungkusnya dengan fungsi IFERROR untuk menangani masukan angka seperti baris 6 di atas yang menyebabkan keluaran kesalahan (karena rumus REGEX hanya bekerja dengan string teks)

Langkah 4

[0-9]2

Contoh 6. Susun Ulang Nama String Dengan REGEXREPLACE

Dalam contoh ini, kami melihat fungsi REGEXREPLACE dan konsep kunci dalam ekspresi reguler. menangkap kelompok

Misalkan kita memiliki daftar nama dalam format ini. “Nama Belakang, Nama Depan” dan kami ingin mengganti urutannya menjadi “Nama Depan Nama Belakang”

Berikut rumus untuk mengganti urutan nama

[0-9]_3

Mari kita uraikan ini

REGEXREPLACE menemukan semua substring yang cocok dengan pola dan menggantinya dengan nilai yang diberikan. Dibutuhkan 3 argumen. 1) teks input, 2) pola yang cocok, dan 3) nilai pengganti

Mari kita lihat masing-masing secara bergiliran

Masukkan teks

Nama keluarga, Nama depan e. g. Collin, Ben

Pola yang cocok

[0-9]_4

[0-9]5

cocok dengan karakter kata

[0-9]6

membuat grup penangkap bernomor. Itu cocok dengan kata pertama hingga koma

[0-9]7

membuka grup yang tidak menangkap, yang pada dasarnya berarti mencocokkan tetapi tidak menangkap teks dalam grup ini

[0-9]_8

adalah grup yang tidak menangkap yang cocok dengan koma dan spasi tetapi tidak menangkapnya

[0-9]6

membuat grup penangkap bernomor kedua. Itu cocok dengan kata kedua setelah koma dan spasi

Nilai Penggantian

\d0

Sekarang, di sinilah menariknya

Pola pencocokan kami menangkap setiap nama sebagai grup bernomor, yang sekarang dapat kami rujuk

\d1

atau

\d2

Grup

\d1

menangkap nama keluarga dan grup

\d2

menangkap nama depan

Untuk membalikkan nama, kami menempatkan grup

\d2

pertama kemudian

\d1

Outputnya terlihat seperti ini

SOLUSI Dari Contoh 1

Modifikasi rumus REGEXMATCH menjadi

\d_7

Ini menggunakan pengukur

\d8

dan batas kata

[0-9]0

untuk hanya mencocokkan angka 4 digit (lihat contoh 5 untuk informasi lebih lanjut tentang bilangan dan batas kata)

Bagaimana Anda menggunakan fungsi pencocokan di Google Sheets?

Bagaimana cara membandingkan dua string di Google Sheets?

Bagaimana cara menggunakan ekspresi reguler di Google?