Replace text with a regex pattern
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])| Parameter | Description |
|---|---|
text |
The text value to process. |
pattern |
The pattern to replace. |
replacement |
The text to replace with. |
occurrence |
[optional] The instance to replace. Default = 0 = all instances. |
case_sensitivity |
[optional] 0 = Case sensitive, 1= Case-insensitive. Default is 0. |
To use REGEXREPLACE, provide a text string, a regex pattern, and the replacement text. For example, to replace "t" with "b" in the text string "tuttle
=REGEXREPLACE("tuttle","t","b") // returns "bubble"The formula replaces all three instances of "t" with "b". Note that REGEXREPLACE is case-sensitive by default. If we capitalize the first "t", only th
=REGEXREPLACE("Tuttle","t","b") // returns "Tubble"To disable case sensitivity, provide a 1 for the case-sensitive argument:
=REGEXREPLACE("Tuttle","t","b",,1) // returns "bubble"With case sensitivity disabled, all three "t"s are replaced with "b". Another way to replace both "T" and "t" with "b" is to include both in a regex c
=REGEXREPLACE("Tuttle","[Tt]","b") // returns "bubble"The REGEXREPLACE function provides an easy way to remove non-numeric characters from a text string. You can see an example below, where REGEXREPLACE i
=REGEXREPLACE(B5,"[^0-9]","")REGEXREPLACE can perform certain transformations, for example making text upper or lower case. You can see an example of how this works in the workshe
=REGEXREPLACE(B5,"^(.)","\U$1")