Description
The REGEXMATCH function tests whether a text value matches a regular expression.
Usage
REGEXMATCH(text, regular_expression)
Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| text | Text | Yes | The text to be tested against the regular expression. |
| regular_expression | Text | Yes | The regular expression to test the test against |
Result
- TRUE if the text matches the given regular expression
- FALSE if the text does not match the given regular expression
- BLANK if the regular expression is invalid
Examples
Validating a registration number
If you want to collect registration numbers that always start with "RN", followed by 9 or 10 digits, you could use the formula:
REGEXMATCH(REG_NUMBER, "^RN[0-9]{9, 10}$")
Note that we use ^ and $ to ensure that we match the whole input and not just part. Otherwise "ZZRN123456789" would also match because the pattern RN[0-9]{9, 10} is present in the input, even if there is extra text before or after.
Validating phone numbers
As described in the validating phone numbers tutorial, you can use regular expression to validate phone numbers collected from participants. To validate a DRC mobile number, you could write:
REGEXMATCH(PHONE_NUMBER, "^((8[0124][0-9])|(9[789][0-9])) [0-9]{3} [0-9]{3}$")
Validating writing script
In some cases, you might want to collect a person's name both in the Latin script and the Arabic script. If you have a form with text fields NAME_EN and NAME_AR, you could use the following validation formula to ensure that only English is entered into the first field:
REGEXMATCH(NAME_EN, "^[a-zA-Z ]+$")
And this formula to ensure that only Arabic characters are used:
REGEXMATCH(NAME_AR, "^[\u0621-\u064A\u0660-\u0669 ]+$")
The ranges \u0621-\u064A and \u0660-\u0669 refer to the range of the Arabic code block in the Unicode character set.