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 common regular expression patterns with practical examples.

Basic Syntax of REGEXREPLACE

=REGEXREPLACE(text, pattern, replacement)

Example:

=REGEXREPLACE(“abc123”, “[0-9]+”, “X”)

In this example, the numeric part of the string “abc123” ([0-9]+) is replaced with “X”, resulting in “abcX”.

List of Regular Expressions with Examples

Below are common regular expression patterns along with their explanations and usage in REGEXREPLACE.

Regular Expression Pattern	Description	REGEXREPLACE Example	Result
`.`	Any single character	`=REGEXREPLACE("abc", ".", "X")`	XXX
`[abc]`	Any one of a, b, or c	`=REGEXREPLACE("abc", "[abc]", "X")`	XXX
`[^abc]`	Any character except a, b, or c	`=REGEXREPLACE("abcd", "[^abc]", "X")`	abcX
`[a-z]`	Any character from a to z	`=REGEXREPLACE("hello", "[a-z]", "X")`	XXXXX
`[0-9]`	Any digit from 0 to 9	`=REGEXREPLACE("123abc", "[0-9]", "X")`	XXXabc
`\d`	Any digit (`[0-9]`)	`=REGEXREPLACE("abc123", "\\d", "X")`	abcXXX
`\D`	Any non-digit character	`=REGEXREPLACE("abc123", "\\D", "X")`	XXX123
`\w`	Any alphanumeric character or underscore	`=REGEXREPLACE("abc_123", "\\w", "X")`	XXXXXXX
`\W`	Any non-alphanumeric character or underscore	`=REGEXREPLACE("abc!123", "\\W", "X")`	abcX123
`^`	Beginning of the string	`=REGEXREPLACE("abc123", "^a", "X")`	Xbc123
`$`	End of the string	`=REGEXREPLACE("abc123", "3$", "X")`	abc12X
`a*`	Zero or more occurrences of a	`=REGEXREPLACE("aaa123", "a*", "X")`	XX123
`a+`	One or more occurrences of a	`=REGEXREPLACE("aaa123", "a+", "X")`	X123
`a?`	Zero or one occurrence of a	`=REGEXREPLACE("aaa123", "a?", "X")`	XXXX123
`a{3}`	Exactly 3 occurrences of a	`=REGEXREPLACE("aaa123", "a{3}", "X")`	X123
`a{2,}`	At least 2 occurrences of a	`=REGEXREPLACE("aaaa123", "a{2,}", "X")`	X123
`a{2,4}`	Between 2 and 4 occurrences of a	`=REGEXREPLACE("aaaa123", "a{2,4}", "X")`	X123
`(abc)`	Group abc	`=REGEXREPLACE("abc123", "(abc)", "X")`	X123
`a\|b`	a or b	`=REGEXREPLACE("abc123", "a\|b", "X")`	XXc123

Conclusion

With Google Sheets’ regular expressions and the REGEXREPLACE function, you can efficiently perform complex text manipulations. Use the patterns presented here to handle your data with ease!