Introduction to Regex Rules and Syntax in Google Sheets.
Regex rules and Syntax in Google Sheets
A Regular Expression is a sequence of characters that specifies a search pattern in the text.
In this article, we would read about regex rules and syntax in Google Sheets that can be used whilst making a REGEX expression. This list contains a lot of rules that you can use with different functions in Google Sheets. This article explains the use and need for RegEx.
Type of Regular Expressions
Anchors
- ^ : This is called caret. It returns the text if the text matches the start of the line or string that the regular expression is searching for. For example, a search with the REGEX expression rule: ^hello would return any text beginning with “hello”.
- $: (dollar) It matches an expression that ends with the search expression. For example, if we search for bye&, it will match an expression ending with “bye”.
MetaCharacters
- . : (dot) It matches any single character, anything except a new line.
- | : (pipe) It matches two alternative options. (book|pen will match any of the strings “book” or “pen”)
- \ : (slash) Matches any literal instead of a character. For example, \* will match a *. “*” is a literal, not a character.
Character Classes
- […] : Matches any character from the set of characters from the brackets. If there are lot of characters in a series you can use a ‘-’ to separate. For example [abc] would match any of ‘a’, ‘b’, ‘c’. [a-z] would match any of the characters from ‘a’ to ‘z’.
- [^…] : Matches any character not in the set of characters. For eg, [b-k] would match any character not in the set from ‘b’ to ‘k’.
- [:alpha:]: Matches characters contained in the alphabet set. It includes characters from ‘a’ to ‘z’ and ‘A’ to ‘Z’. Whilst using it, use it in double brackets [[:alpha:]].
- [:digit:]: Matches the digits. Includes ‘0’ to ‘9’. Like alpha, it should be used in another set of square brackets [[:digit:]].
- [:alnum:]: Matches alphanumeric characters (letters or numbers). It includes a-z, A-Z and 0-9. Like the previous entries, it should also be used in two square brackets [[:alnum:]]
- [:space:] : Matches all the whitespace characters. It includes all the blanks, newlines, tab space etc. This should be surrounded by double square brackets to be used, for eg [[:space:]].
- [:word:] : Matches any character that can be used in a word (letter, digit or underscore). It also should be used in two square brackets, for eg [[:word:]].
- [:punct:] : Matches punctuation characters and symbols. It also should be used in two square brackets, for eg [[:punct:]].
- [:graph:] : Matches characters that are visible. All characters excluding spaces, newlines, control characters and so on. It also should be used in two square brackets, for eg [[:graph:]].
- [:print:] : Matches visible characters and spaces. It also should be used in two square brackets, for eg [[:print:]].
Shorthand Character Classes
- \w: Matches any word character. It is the same as [:word:]. It includes a-z, A-Z, 0-9, or _ .
- \W: Matches any non-word character. Any character which is not aletter, digit or underscore.
- \s: Matches any whitespace, equivalent to [:space:].
- \S: Matches any character except whitespaces. Equivalent to [^[:space:]].
- \d: Matches any character from 0-9, equivalent to [:digit:].
- \D: Matches any character, except 0-9. It is equivalent to [^[:digit:]].
Quantifiers
- {n}: Match the preceding expression n times. For example, [b-d]{3} would match “bc” or “cd” and so on.
- {n, m}: Match the preceding expression occurring at least n times and at max m times. Thus the expression [b-d]{1-3} would match “b”, “bc”, “bdd”, but not “bcdc”.
- ? : Match the preceding expression 0 or 1 time. For example the expression “a1?” Would match either “a” or “a1” since ‘1’ is optional.
Group
- (…): Groups parts of an expression. You can use grouping to apply quantifiers to groups. You can also use them to match character classes before or after a group.
Additional Regex Rules
- * : Matches 0 or more characters, for example, ab* would match “ab”, “abcd”, “abfgh” but not “aob”.
- + : Similar to “*” but matches one or more characters.
Conclusion
We saw many Regex rules and syntax in Google Sheets which can be used not only in Google Sheets but also in other tools and technologies. They can be used with google sheets or mostly with all the google services. All this combined should cover almost all of the cases required in day to day life.
There might be other Regex rules and syntax in Google Sheets but this list should contain all the rules that you’ll need in day-to-day usage.
See Also
Validate URLS in Google Sheets: Learn how to Validate URLs using Conditional Formatting in Google Sheets
Google Sheets Left and Right Functions : Learn how to use the LEFT and RIGHT Functions in Google Sheets.