The REGEXMATCH function is a powerful text function in Google Sheets that allows you to match patterns using regular expressions. While it is commonly used for text manipulation, it can also be used to match dates. In this tutorial, I will guide you through the process of using REGEXMATCH to match single and multiple dates in Google Sheets.
Understanding the REGEXMATCH Function
The REGEXMATCH function takes two arguments: text and regular_expression. It is important to note that both arguments must be pure text values. Failure to provide text values will result in a VALUE! error. Therefore, when working with dates, we need to convert them to plain text before using them in REGEXMATCH.
Below is a sample date column in Google Sheets:
Let’s say we want to check if the date 10/09/2018 in cell D2 is present in the range A2:A10. We might be tempted to use the following formula:
=REGEXMATCH(A2:A10, D2)However, this approach is incorrect. To match a single date using REGEXMATCH in Google Sheets, we need to convert the dates to plain text using the TO_TEXT function. The correct formula would be:
=ArrayFormula(REGEXMATCH(TO_TEXT(A2:A10), TO_TEXT(D2)))
It is worth noting that if the date formatting differs between the column A and column D, the formula may not produce accurate matches. To address this, you can either include the TEXT function to format the dates or manually select the dates and format them using the “Format > Number > Custom Date and Time” option.
Matching Multiple Dates or Date Range Using REGEXMATCH
To match multiple dates using REGEXMATCH in Google Sheets, we need to combine the dates into a regular expression pattern. Each date should be separated by a pipe symbol. For example:
=ArrayFormula(REGEXMATCH(TO_TEXT(A2:A10), "15/09/2019|17/10/2019|02/11/2019"))
If you want to use the dates in the range D2:D4 instead of manually typing them in the regular expression, you can use the TEXTJOIN function to combine the dates:
=ArrayFormula(REGEXMATCH(TO_TEXT(A2:A10), TEXTJOIN("|", TRUE, D2:D4)))Filtering Matching Multiple Dates Using REGEXMATCH
The formulas provided above for matching multiple dates using REGEXMATCH yield Boolean TRUE and FALSE values as output. To filter the rows that match the dates, you can use the FILTER function:
=FILTER(A2:B10, ARRAYFORMULA(REGEXMATCH(TO_TEXT(A2:A10), TEXTJOIN("|", TRUE, D2:D4))))
Please note that the inclusion of ARRAYFORMULA around the REGEXMATCH is not mandatory within the FILTER function. You can alternatively use the FILTER formula without it:
=FILTER(A2:B10, REGEXMATCH(TO_TEXT(A2:A10), TEXTJOIN("|", TRUE, D2:D4)))That wraps up the discussion on matching dates using REGEXMATCH in Google Sheets. Feel free to explore the additional resources below for more tips and tricks. And remember, if you want to master Google Sheets and take your data analysis skills to the next level, be sure to check out Crawlan.com for more tutorials and guides!
Additional Resources:
- REGEXMATCH in SUMIFS and Multiple Criteria Columns in Google Sheets
- Match Two Columns that Contain Values Not in Any Order Using Regex
- Multiple OR in Conditional Formatting Using Regex in Google Sheets
- Matches Regular Expression Match in Google Sheets Query
- Highlight Matches or Differences in Two Lists in Google Sheets
