REGEXEXTRACT Function: Extract Text Using Regular Expressions in Google Sheets

スポンサーリンク
スポンサーリンク

About the REGEXEXTRACT Function

Overview of REGEXEXTRACT

Text Extraction Using Regular ExpressionsGoogle Sheets Function

=REGEXEXTRACT( text, regex )

Summary The REGEXEXTRACT function extracts the first matching substring based on a given regular expression.

  • Searches text flexibly using regular expressions (Regex).
  • Ideal for extracting specific portions of data.
  • Combine with other functions (e.g., IFERROR) for error handling.

When to Use REGEXEXTRACT

  • When you need to extract specific parts of a string.
  • When analyzing data based on patterns.
  • When retrieving parts of structured data (e.g., emails, phone numbers).

How to Use REGEXEXTRACT

The following table demonstrates the basic usage of the REGEXEXTRACT function.

  A B
1 Text Extraction Result
2 abc-123 =REGEXEXTRACT(A2, “[0-9]+”)
3 hello@domain.com =REGEXEXTRACT(A3, “[a-z]+”)

Results

  • In cell B2, the regular expression [0-9]+ matches 123.
  • In cell B3, the regular expression [a-z]+ matches hello.

Regular Expression Reference

Google Sheets Regular Expressions: Examples with REGEXREPLACE Function
By using the REGEXREPLACE function in Google Sheets, you can easily perform text replacement based on regular expressions. This article explains nearly all comm...

Advanced Examples of REGEXEXTRACT

Combine REGEXEXTRACT with other functions for more flexible data processing.

Example 1: Extract Domain Names

Extract the domain part of an email address.

  A B
1 Email Address Domain
2 user@example.com =REGEXEXTRACT(A2, “@(.+)$”)
  • In this example, example.com is extracted.

Example 2: Extract Year from Dates

Extract the year from a date in the format YYYY-MM-DD.

  A B
1 Date Year
2 2024-12-20 =REGEXEXTRACT(A2, “^[0-9]{4}”)
  • In this example, 2024 is extracted.

Points to Note

  • If no match is found, the function returns a #N/A error.
  • Use the IFERROR function to handle errors gracefully.
  • Understanding regex syntax is key to leveraging this powerful tool effectively.

Conclusion

  • The REGEXEXTRACT function is a powerful tool for extracting specific parts of data using regular expressions.
  • Combining it with other functions enables even more flexible data manipulation.