Tips: Coba gunakan fungsi XMATCH baru, versi MATCH yang ditingkatkan yang bekerja ke arah mana saja dan mengembalikan kecocokan persis secara default, memudahkan dan lebih mudah digunakan daripada sebelumnya. Fungsi MATCH mencari item yang ditentukan dalam rentang sel, kemudian mengembalikan posisi relatif item tersebut dalam rentang. Sebagai contoh, jika rentang A1:A3 berisi nilai 5, 25, dan 38, rumus =MATCH(25,A1:A3,0) akan mengembalikan angka 2, karena 25 merupakan item kedua dalam rentang tersebut. Tips: Gunakan MATCH ketimbang salah satu dari fungsi LOOKUP ketika Anda membutuhkan posisi sebuah item dalam rentang dan bukannya item itu sendiri. Misalnya, Anda mungkin menggunakan fungsi MATCH untuk menyediakan sebuah nilai untuk argumen row_num dari fungsi INDEX. SintaksMATCH(lookup_value, lookup_array, [match_type]) Sintaks fungsi MATCH memiliki argumen berikut:
Match_type Perilaku 1 atau dikosongkan MATCH menemukan nilai terbesar yang lebih kecil dari atau sama dengan lookup_value. Nilai dalam argumen lookup_array harus disusun dalam urutan naik, misalnya: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. 0 MATCH menemukan nilai pertama yang sama persis dengan lookup_value. Nilai dalam argumen lookup_array dapat disusun dalam urutan apa saja. -1 MATCH menemukan nilai terkecil yang lebih besar dari atau sama dengan lookup_value. Nilai di argumen lookup_array harus diletakkan dalam urutan turun, misalnya: TRUE, FALSE, Z-A, ... 2, 1, 0, -1, -2, ..., dan sebagainya.
ContohSalin contoh data di dalam tabel berikut ini dan tempel ke dalam sel A lembar kerja Excel yang baru. Agar rumus menunjukkan hasil, pilih datanya, tekan F2, lalu tekan Enter. Jika perlu, Anda bisa menyesuaikan lebar kolom untuk melihat semua data. Produk Jumlah Pisang 25 Jeruk 38 Apel 40 Pir 41 Rumus Deskripsi Hasil =MATCH(39,B2:B5,1) Karena tidak ada kecocokan persis, posisi nilai terendah berikutnya (38) di rentang B2:B5 dikembalikan. Experience all of the benefits of the classic spreadsheet with much more powerful features that save users time!
REGEXMATCH function is one of the most underrated functions of Google Sheets. Most users often choose not to use it as they think it’s probably going to be too complex. The presence of regular expressions in the formula often puts people off. However, with a little guidance, you can begin to incorporate the function into your spreadsheets and reap its benefits. In this tutorial, I will cover how to use the REGEXMATCH function in Google Sheets and show you some simple examples that will further help you understand how to use it in your day-to-day work. Table of Contents What does the REGEXMATCH Function Do?The REGEXMATCH function belongs to Google Sheets’ suite of REGEX functions along with functions like REGEXEXTRACT and REGEXREPLACE. Its main task is to find if a string of text matches a regular expression. The function returns a TRUE if the text matches the regular expression’s pattern and a FALSE if it doesn’t. What is a Regular Expression (REGEX)?A regular expression is a text pattern. It is a sequence of characters, including symbols, letters, and numbers that are combined to form a search string. A regular expression is often characterized by certain special symbols, also known as ‘metacharacters’. The metacharacters are meant to represent either a single character, a sequence of characters, or one of the characters in a set. Here are some metacharacters that the REGEXMATCH function supports: Metacharacter What it Represents A regular expression containing a combination of these metacharacters, along with other alphanumeric and/or special characters can help you find specific characters, strings, or patterns in a text string. For example, the regular expression ‘^gr(a|e)y$’ matches strings that start with ‘gr’ followed by either an ‘a’ or an ‘e’ and ends with a ‘y’. Syntax of the REGEXMATCH FunctionThe syntax for the REGEXMATCH function is as follows: REGEXMATCH(text, reg_exp) Here,
Applications of the REGEXMATCH Function (Examples)The REGEXMATCH function can be quite helpful when you want to search for the existence of a particular search string or pattern in the contents of a cell. Here are some useful applications of the REGEXMATCH function:
There are many other ways in which you can apply the REGEXMATCH function. Once you start getting comfortable with REGEXMATCH and regular expressions, you will find that the possibilities are endless. Let us see how REGEXMATCH can be used in each of the above applications Using the REGEXMATCH Function to Identify Cells that Contain a Letter, Word, or PhraseLet’s first see how you can use REGEXMATCH to identify if a cell contains a specific letter, word, or phrase. If you want to know if cell A1 contains the word “good” then use the REGEXMATCH function as follows: =REGEXMATCH(A1,"good") If you want to know if cell A1 contains any of the words ‘good’ , ‘wonderful’ or ‘amazing’, then that’s easy too. We know that the metacharacter ‘|’ represents an Or operation. So you need to use the REGEXMATCH with a regular expression as shown below: =REGEXMATCH(A1, "good|wonderful|amazing") If you want to know if cell A1 contains numbers between 0-9, then you will need to use the square brackets as shown: =REGEXMATCH(A1, "[0-9]") In the below example, it returns FALSE and there’s no number in cell A1. In the below example, it returns TRUE, as there’s a number in cell A1. Using the REGEXMATCH Function to Identify Cells that Start or End with a Specific TextIf you want to know if cell A1 starts with the word ‘you’ or ‘You’, then you need to use the ‘^’ metacharacter( since it represents the beginning of a string) along with the ‘|’ metacharacter. So the function you will need to use is: =REGEXMATCH(A1, "^you|You")
If you want to know if cell A1 ends with the word ‘you’ or ‘You’, then you need to use the ‘$’ metacharacter( since it represents the end of a string) along with the ‘|’ metacharacter. So the function you will need to use is: =REGEXMATCH(A1, "you|You$") If you want to know if cell A1 starts with ‘You’ and ends with ‘good’ then you need to use a combination of both ‘^’ and ‘$’ metacharacters: =REGEXMATCH(A1, "^You[a-zA-Z ]+good$")
Here, we added “[a-zA-Z ]+” in the middle to represent any number of alphabets, both in lower and upper case, in between the words ‘You’ and ‘great’. Note that there is a space after the ‘Z’ too because we also want to allow spaces to be present in the sequence. The ‘+’ sign represents any number of letters and/ or spaces. Using the REGEXMATCH Function to Find the Exact Match of a StringLet’s say you want to know if cell A1’s contents exactly match the string “you” For this, all you need to do is use the REGEXMATCH function as follows: =REGEXMATCH(A1,"^you$") We get a FALSE in the result shown above because cell A1 contains the word “you” but does not contain only the word “you”. Using the REGEXMATCH Function to Identify Hashtags among Social Media PostsLet us say you have a set of social media posts in different cells of a column and you want to identify only those posts with hashtags in them. For this, you will need to find if each cell has the character ‘#’ in it. So, you can perform the following steps:
Using the REGEXMATCH Function to Find Email Addresses in a RangeLet us see one final example of how powerful the REGEXMATCH function can be. Let us suppose you want to find all the cells in a range (shown in the image above) that contain email addresses. For this, we first need to put together a regular expression for the pattern of an email address. We know that:
For the above rules, we can formulate a regular expression as follows:
Note: a-z represents any lowercase character, while A-Z represents any uppercase character. 0-9 represents any numeric character between 0 and 9. The plus metacharacter (+) specifies that there can be one or more occurrences of the characters mentioned within the square brackets []. So our final regular expression can be: ^[a-zA-Z0-9.-_]+@[a-zA-Z0-9]+\.[a-zA-Z]+$ This is not perfect and completely foolproof, but to keep things simple, let’s go with it. It should work with our sample data. Now let us put this to use in our sample dataset. Follow these steps:
Now you can clearly see which of the cells contain email addresses. Subsequently, you could use a filter to display only the cells that contain email addresses if required. These were a few simple examples of how the REGEXMATCH function can save time and increase efficiency. Points to RememberHere are a few important points that you need to remember when using the REGEXMATCH function.
The REGEXMATCH function can have numerous applications, once you learn how to use it effectively. Strong knowledge of regular expressions helps, and the best way to get a good hold over it is to practice. Play around with different regular expressions and see what results you get. You will be surprised by how useful the REGEXMATCH function can be once you start using it on your day-to-day spreadsheet data. I hope you found this tutorial useful! Other Google Sheets Tutorials you may find useful:
Most Popular Posts5 Simple Ways to Highlight Duplicates in Google SheetsIF CONTAINS Google Sheets Formulas [2 Clever Options]How to Apply Formula to Entire Column in Google SheetsHow to VLOOKUP From Another Sheet in Google SheetsSumitSumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people. 1 thought on “REGEXMATCH Function in Google Sheets (Easy Examples)”
|