REGEXEXTRACTformula is used to extract a portion of a text string that matches a regular expression. This is useful when you need to extract specific information from a text string that follows a certain pattern.
- How to use
- Examples of using
REGEXEXTRACTformula not working?
- Similar formulas to
REGEXEXTRACT formula with the syntax shown below, it has 2 required parameters:
- text (required):
The text string from which you want to extract a portion of text.
- regular_expression (required):
The regular expression used to match the portion of text you want to extract.
ExamplesHere are a few example use cases that explain how to use the
REGEXEXTRACTformula in Google Sheets.
Extracting email addresses
You can use the
REGEXEXTRACT formula to extract email addresses from a list of text. For example, if you have a column of text that includes email addresses, you can use a regular expression to extract only the email addresses into a separate column.
Extracting phone numbers
Similar to extracting email addresses, you can use the
REGEXEXTRACT formula to extract phone numbers from a list of text. For example, if you have a column of text that includes phone numbers, you can use a regular expression to extract only the phone numbers into a separate column.
If you have a list of text that includes URLs, you can use the
REGEXEXTRACT formula to extract only the URLs into a separate column. This is useful when you need to analyze or sort URLs separately from other text.
You can use the
REGEXEXTRACT formula to extract numbers from a list of text. For example, if you have a column of text that includes both numbers and text, you can use a regular expression to extract only the numbers into a separate column.
REGEXEXTRACTnot working? Here are some common mistakes people make when using the
REGEXEXTRACTGoogle Sheets Formula:
Not using the correct regular expression pattern
One common mistake when using the
REGEXEXTRACT function is not using the correct regular expression pattern to match the desired text. Ensure that your regular expression is written correctly and matches the pattern you are trying to extract.
Forgetting to include capturing groups
If you want to extract a specific portion of the matching pattern, you need to include capturing groups in your regular expression. Forgetting to include capturing groups will result in the function returning the entire matching pattern.
Not accounting for case sensitivity
When using the
REGEXEXTRACT function, it is important to consider the case sensitivity of your regular expression. If your regular expression is case sensitive and the text string contains uppercase or lowercase characters that do not match, the function will not return a matching pattern.
The following functions are similar to
REGEXEXTRACT or are often used with it in a formula:
REGEXMATCHformula searches for a regular expression pattern within a specified text string, and returns a boolean value of TRUE if the pattern is found, and FALSE if it is not found. This formula is commonly used to check if a string matches a certain pattern or to extract specific information from a text string based on a pattern.
REGEXREPLACEformula is used to replace a piece of text that matches a regular expression with a new piece of text. This formula is useful when working with text data that needs to be cleaned or manipulated based on specific patterns or rules.
SPLITformula in Google Sheets is used to split a text string into separate cells based on a delimiter. It is commonly used when you have a large text string that you need to break down into smaller parts. The resulting parts are placed into individual cells, making it easier to work with and analyze the data.
LEFTformula is used to extract a specific number of characters from the beginning of a text string. It is most commonly used to extract the first name or last name from a full name or to extract a date from a text string.
RIGHTformula in Google Sheets returns the rightmost characters from a string, based on the number of characters specified. It is commonly used to extract a certain number of characters from the end of a string. The formula takes two arguments: the string from which to extract the characters, and the number of characters to extract.
You can learn more about the
REGEXEXTRACT Google Sheets function on Google Support.