REGEXREPLACE function replaces part of a text value that matches a regular with expression with new text.
REGEXREPLACE(text, regular_expression, replacement)
|text||Text||Yes||The text, a part of which will be replaced|
|regular_expression||Text||Yes||The regular expression. All matching instances in
|replacement||Text||Yes||The text which will be inserted into the original text|
- A new STRING with any matching parts replaced.
REGEXREPLACE can be useful in cleaning collected data. For example, if you had previously collected numeric value in an open text field with code
FAMILY_COUNT, and some of your staff used the period as the thousands seperator (e.g. "4.000" and "3.500") and wanted to convert the values to numbers using the VALUE.
VALUE(REGEXREPLACE(FAMILY_COUNT, ",", "."))