Try using mapflo for data analysis. It has regex for data matching built in. Get Started for Free.


Summary

Regular expression (also known as regex or regexp) makes it easy to see if a string contains certain patterns of characters (as well as optionally extracting or replacing the matched pattern).

It’s really useful for things like segmenting URLs based on terms the URL contains (for example extracting and categorizing by utm values).

Surprisingly, Excel does not support Regex but Google Sheets has three Regex functions.

In this article we explain how to use Regex in Google Sheets and provide examples of how different regular expressions match to a string.

Regex in mapflo

If you want to use Regex to analyze data then try mapflo. mapflo puts data into a flow and uses nodes to join, shape and clean the data.

The step-by-step video below shows how easy it is to categorise search terms using simple Regex and separately extract a product ID from a URL using a more complex match.

[insert video – regex example categorize keyword planner data]

[insert video – ]

 

Learn more about mapflo

 

Regex in Google Sheets

Google Sheets supports three regex functions:

  • REGEXMATCH – Is there a pattern match in the string? (TRUE or FALSE)
  • REGEXEXTRACT – Extract the pattern match from the string
  • REGEXREPLACE – Replace the pattern match in the string

You can see examples of how to use these three formulas in Google Sheets below

 

 

Regex in Google Sheets – detail

Google Sheets supports three regex formulas

  • REGEXMATCH – does the sample text match the regex expression
  • REGEXREPLACE – replace the matched string with another string
  • REGEXEXTRACT – extract the matched string

Note:

  • the text/string you are searching in must be in text format
  • Google Sheets supports only the following syntax (R2) 

Syntax

  • REGEXMATCH(text, regular_expression)
  • REGEXEXTRACT(text, regular_expression)
  • REGEXREPLACE(text, regular_expression, replacement)

refeerences can be references or written into the syntax.

Examples:

The table below shows the output from REGEXMATCH, REGEXEXTRACT and REGEXREPLACE for the text ‘!WE26 49’ and the regular expression character class examples:

The highlighted row has the following formulas:

REGEXMATCH(“!WE26 49″,”\d+”) = TRUE
REGEXEXTRACT(“”!WE26 49″,”\d+”) = 26
REGEXEPLACE(“”!WE26 49″,”\d+”,”X”) = !WEX X

Anchor examples:

 

Quantifier examples:

 

Greedy and Lazy match: