Zoho Sheet REGEXREPLACE Function: All, Numbered and Last Matches

The official REGEXREPLACE reference was retrieved October 15, 2026. Zoho’s release history announces REGEXMATCH, REGEXEXTRACT and REGEXREPLACE in January 2025. That establishes an announcement month, not a numbered first build or proof that every currently documented optional argument existed on that date. The last functioning version is unknown: current documentation is not a last-working-build test. The web entry does not establish mobile or offline-client parity.

The worked results and constraints below were independently checked. No native Zoho session was executed. Examples follow the help’s semicolon formula notation even where its syntax heading uses commas; verify the actual editor’s accepted separators. All datasets are fictional and fully specified rather than reconstructed from hidden embedded cells.

REGEXREPLACE(text; regular_expression; replacement; [occurence]; [case_sensitivity])

The help spells the optional fourth parameter occurence in its syntax heading; the function name remains REGEXREPLACE. text is the source string, regular_expression is the pattern and replacement supplies inserted text.

Occurrence defaults to 0, replacing all matches. A positive occurrence selects that numbered match from the beginning. A negative occurrence searches from the end: -1 targets the last matching instance. case_sensitivity defaults to 0, case-sensitive, while 1 is case-insensitive.

These examples use simple ASCII patterns and literal replacement text. The entry does not identify a regex engine or specify replacement backreference syntax, so no claim is made that $1, \1, lookbehind or another product’s extension works in Zoho.

=REGEXREPLACE("Hello 123";"[0-9]+";"000") has expected text Hello 000, matching Zoho’s visible example. The omitted occurrence means all matches, but this input contains only one digit run.

A single-match control cannot distinguish all-occurrence replacement from first-only replacement. Use the multi-match fixture below when auditing an imported formula. The quotes around 000 preserve it as the intended replacement text.

Put A12 B345 C6 in A2. Enter the following formulas in separate result cells:

The occurrence count concerns matches, not individual characters. The run 345 is one match for [0-9]+. Replacing that run inserts a single #, rather than three copies.

The examples use nonoverlapping, nonempty matches. Empty-string matching and overlapping-pattern behavior are not established by these fixtures or the abbreviated reference.

Use input cat CAT Cat with pattern "cat" and replacement "dog". Occurrence 0 and case_sensitivity 0 independently produce dog CAT Cat. Changing only the final option to 1 produces dog dog dog.

The replacement text remains exactly dog in the model; case-insensitive matching does not request the replacement to imitate each original capitalization. If that transformation is needed, define and test it separately rather than assuming it follows from the match flag.

For a fictional identifier-cleanup rule, use =REGEXREPLACE("part---07--blue";"-+";"-";0;0). The independent result is part-07-blue. The pattern selects each nonempty run of hyphens and replaces that run once.

This rule intentionally changes punctuation while retaining letters and digits. It is not a general validation rule or a safe rewrite for every identifier system. Keep the original field in a separate column and confirm that collapsing separators does not merge values that are supposed to remain distinct.

As a second deterministic control, replacing repeated ordinary spaces with one ordinary space turns red blue green into red blue green using pattern " +". This does not claim to normalize every Unicode whitespace character or strip leading/trailing spaces.

=REGEXREPLACE("Weight 46.59 kg; spare 7.25 kg";"[0-9]+\.[0-9]+";"0.00";1;0) independently produces Weight 0.00 kg; spare 7.25 kg.

Changing occurrence to -1 replaces the last token instead, producing Weight 46.59 kg; spare 0.00 kg. The escaped dot confines the pattern to decimal tokens; the literal replacement is not evaluated as a numeric rounding formula.

The help’s generic error table mentions delimiters and an instance value of zero, while the function-specific occurrence definition explicitly makes 0 the valid all-matches default. The direct parameter definition is used here; that generic zero-instance row is not copied as a REGEXREPLACE prohibition.

No-match behavior, out-of-range occurrence results, invalid patterns, fractional selectors and unsupported case modes were not native-tested. Keep a multi-match control with distinct tokens, inspect the sign of occurrence and check literal backslashes and quotes. A replacement can produce text that still violates the intended policy, so validation is a separate step.