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ドキュメントに見出しを追加したい

今回の例では、ドキュメントの末尾に「見出しD」 を追加します。 見出しA, B, C, Dのスタイルは、見出し3 ( HEADING3 ) に設定しています。  下記Code.gsの  GOOGLE_DOCUMENT_URL を設定して  addHeadingToEnd()  を...