HomeCasa Prompts

Regex formulas for Google Sheets and Excel, explained and tested

Extract, validate or replace text with REGEXEXTRACT, REGEXMATCH and REGEXREPLACE: the exact formula for your examples, explained piece by piece, tested on edge cases and applied to a whole column. Designed for ChatGPT, Claude, Gemini and Microsoft Copilot. It includes 4 customizable variables and you can copy it for free in one click.

Intermediate · Text · Pending editorial review

Designed for:

Prompt

Act as a spreadsheet and regular expression expert who explains every pattern so it can be maintained later.

My case:
- What I want to do: {{task}}
- Input examples and expected result (one per line, input → output): {{examples}}
- Tool: {{tool}}
- Argument separator in my settings: {{separator}}

If the examples are ambiguous or contradict each other, ask me before writing the pattern.

Deliverables:
1. The final single-cell formula, ready to paste, using my separator.
2. A piece-by-piece explanation of the pattern (anchors, character classes, quantifiers, groups).
3. A test table: each example with the result the formula will return; flag any example it does not satisfy.
4. Edge cases: empty cells, spaces, upper and lower case, accented characters, multiple matches, and how to avoid errors (for example, wrapping in IFERROR).
5. A version for the whole column (ARRAYFORMULA in Google Sheets or a spilled array in Excel).
6. A non-regex alternative if a simpler one exists.

Constraints:
- Respect each tool's engine: Google Sheets uses RE2 (no lookahead or lookbehind); Excel's regex functions are only available in recent Microsoft 365 builds, so say how to check and what to do if I do not have them.
- Use the function names that match the tool and warn me if they change with the installation language.
- Do not claim anything is tested unless you have checked it against my examples.

Format: the formula in a code block, a test table and short notes.

How to use it

Give at least five varied examples, including the odd ones. Paste the formula into a cell, check the test table against your real data and then use the whole-column version.

Usage example

Task: extract the ZIP code from a US address · Example: "350 Fifth Ave, New York, NY 10118" → 10118 · Tool: Google Sheets · Separator: comma.

Sample output

Final formula (Google Sheets, comma separator)

=IFERROR(REGEXEXTRACT(A2, "\b(\d{5})(?:-\d{4})?\s*$"), "")

How the pattern works

  • \b → word boundary, so it does not grab digits from a longer number.
  • (\d{5}) → the 5-digit ZIP, the only group returned.
  • (?:-\d{4})? → optional ZIP+4 extension, matched but not returned.
  • \s*$ → must sit at the end of the address, so a 5-digit street number is ignored.
  • IFERROR(…, "") → returns blank instead of #N/A.

Tested against your examples

InputResultPass?
350 Fifth Ave, New York, NY 1011810118✔
1600 Amphitheatre Pkwy, Mountain View, CA 94043-135194043✔
PO Box 12, Austin TX(blank)✔

Edge cases

  • Trailing spaces are handled by \s*; trailing text such as ", USA" is not → wrap A2 in a cleanup first if needed.
  • A ZIP typed as a number loses its leading zero (02108 → 2108): store the column as text.

Whole column

=ARRAYFORMULA(IF(A2:A="", "", IFERROR(REGEXEXTRACT(A2:A, "\b(\d{5})(?:-\d{4})?\s*$"), "")))

Sheets uses RE2: no lookahead or lookbehind, and this pattern needs neither. …

Key facts

Category
Data management · Formulas
Output type
Text
Level
Intermediate
Designed for
ChatGPT, Claude, Gemini and Microsoft Copilot
Verified in
Pending editorial verification
Variables
What I want to do, Examples input → output, Tool and Argument separator
Language
English
Price
Free, no sign-up
Updated
Nov 7, 2026

Frequently asked questions

Does Excel have regex functions?

Recent Microsoft 365 versions include functions such as REGEXTEST, REGEXEXTRACT and REGEXREPLACE, but not every installation has them. The prompt tells you how to check and gives an alternative.

Why does my pattern work on a regex tester but not in Google Sheets?

Because Google Sheets uses the RE2 engine, which does not support some constructs such as lookaheads. The prompt adapts the pattern to that engine.

What is this prompt for?

Extract, validate or replace text with REGEXEXTRACT, REGEXMATCH and REGEXREPLACE: the exact formula for your examples, explained piece by piece, tested on edge cases and applied to a whole column.

Which AI tools does it work with?

It is designed for ChatGPT, Claude, Gemini and Microsoft Copilot. We have not verified it in these tools yet, so results may vary between them.

How do I customize the prompt?

Click “Customize” and fill in the variables (What I want to do, Examples input → output, Tool and Argument separator). The text updates instantly and you can then copy it in one click. Values are stored only in your browser.

Do I need experience to use it?

It is intermediate: it helps to know the topic so you can provide good context and review the result critically.

Is this prompt free?

Yes. Every HomeCasa Prompts prompt is free, with no sign-up or subscription. You can also use them in professional projects.

Can I trust the AI output?

Treat it as a high-quality draft: always check data, figures and important claims before publishing or making decisions.

Related prompts