Apps Scriptリファレンス: Apps Script Reference |障害・課題追跡: IssueTracker |Google Workspace: Status Dashboard - Summary

2023年9月30日土曜日

Extracting data from Google Sheets with regular expressions


Introduction

Regular expressions are a powerful tool that can be used to extract data from text. 
In Google Sheets, regular expressions can be used with the filter function to extract data from a range of cells. 
This can be useful for a variety of tasks, such as filtering data by a specific pattern, extracting specific information from a string, or matching data to a specific format.

In this blog post,  I will give an example of how regular expressions can be used with the filter function to extract data.


Example

1. Select the range you want to filter.
2. Click the filter icon in the toolbar.
3. Select "Custom formula is" from the filter condition drop-down menu.

Filter by condition > Custom formula is


4. Enter the formula =REGEXMATCH(range, "[A-Z]") in the input box.
5. Click OK.


The data will be extracted based on the regular expression that I set.


Tips

=REGEXMATCH(B:B, "[A-Z]")


Reference

Use conditional formatting rules in Google Sheets
https://support.google.com/docs/answer/78413?hl=en&co=GENIE.Platform%3DDesktop

REGEXMATCH
https://support.google.com/docs/answer/3098292?hl=en


Web colors
https://en.wikipedia.org/wiki/Web_colors



Latest post

Googleドライブ内の音声ファイルをiframe内で再生したい

iframe の src にGoogleドライブ内の音声ファイルを埋め込む例です (今回試した音声ファイルはmp3) Code.gs function doGet () { return HtmlService . createTemplateFromFile ( ...