# REGEXEXTRACT in Google Sheets for Statement Text

- Author: FastBankConvert (https://fastbankconvert.com)
- Published: 2026-10-17
- Category: Google Sheets, Numbers and LibreOffice
- Canonical: https://fastbankconvert.com/blog/regexextract-in-google-sheets

> Use REGEXEXTRACT in Google Sheets to pull dates, amounts and references out of statement text, with patterns that you can copy and adapt.
REGEXEXTRACT in Google Sheets returns the part of a text cell that matches a pattern you write, such as a date, an amount or a check number inside a bank description. Write `=REGEXEXTRACT(A2, "pattern")`, fill it down, and wrap the result in VALUE when you need a number, because the function always returns text.

## What REGEXEXTRACT returns from a line of statement text

REGEXEXTRACT takes two arguments: the text to search and a regular expression, which is a short pattern describing what to find. Google's [REGEXEXTRACT help page](https://support.google.com/docs/answer/3098244) says it returns the first part of the text that matches. It accepts text only and returns text only.

Statement text is a good fit because banks pack several facts into one description. A line such as `POS PURCHASE 03/14 FLOUR SUPPLY CO REF 88231 CARD 4417` holds a date, a merchant, a reference and a card number. REGEXEXTRACT lets you pull each into its own column without retyping.

The basic shape is:

`=REGEXEXTRACT(A2, "\d{5}")`

That returns the first run of five digits, here 88231. A few pattern pieces cover most statement work:

- `\d` is one digit, and `\d{5}` is exactly five of them.
- `\s` is a space or other whitespace.
- `+` means one or more of the thing before it, and `*` means zero or more.
- `^` anchors to the start of the text, and `$` to the end.
- `( )` marks a capture group, the part you want returned.

In a Sheets formula, a backslash is typed once. You do not double it as you would in many programming languages.

## REGEXEXTRACT patterns for dates inside descriptions

Banks print dates inside descriptions in a few predictable forms: month and day only, a full date with a year, or year-first. Match the form your bank uses, and anchor the pattern when the date always sits in the same place, so a reference number cannot be mistaken for it.

Common forms and patterns:

- Month and day at the start of the line: `^\d{2}/\d{2}`
- Month and day anywhere, such as a card swipe date: `\d{2}/\d{2}`
- Full date with a two or four digit year: `\d{1,2}/\d{1,2}/\d{2,4}`
- Year first: `\d{4}-\d{2}-\d{2}`
- Month name and day: `(?i)(?:jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)\s+\d{1,2}`

The `(?i)` at the start of the last pattern turns on case-insensitive matching, so "Mar 14" and "MAR 14" both match. The `(?: )` around the month names groups them without making a capture group; a plain `( )` there would make the formula return only the month name. Google Sheets uses the RE2 engine, and the [RE2 syntax reference](https://github.com/google/re2/wiki/Syntax) lists which flags and classes it supports.

A date pulled from a description is often a transaction date, which can differ from the posting date in the statement's date column. Keep both. The gap between them explains many "missing" transactions at month end.

## Matching amounts, signs and parentheses with REGEXEXTRACT

An amount pattern needs digits, optional thousands commas, a decimal point and two decimals. Anchor it to the end of the line when the amount is always last. `-?[\d,]+\.\d{2}$` matches 6.75, 1,240.00 and -42.10 at the end of a cell, and ignores the numbers earlier in the text.

Variations for the forms banks print:

- Dollar sign before the number: `\$?[\d,]+\.\d{2}`. The `\$` is a literal dollar sign; `$` alone means end of text.
- Negative in parentheses: `\([\d,]+\.\d{2}\)` matches (42.10) with the brackets.
- Trailing minus or CR marker: `[\d,]+\.\d{2}\s?(?:-|CR)?$`
- Any amount, signed or bracketed: `\(?-?[\d,]+\.\d{2}\)?`

Matching the brackets is only half the job, because the result is still text with brackets in it. Converting "(42.10)" to the number -42.10 is covered in [handling negative numbers in parentheses in Google Sheets](/blog/negative-numbers-in-parentheses-in-google-sheets).

Do not drop the decimal part from the pattern. `[\d,]+` alone matches the first digits it finds, which on a card line is often the date or the card number.

## Capture groups for check numbers and payment references

A capture group, the part of a pattern inside parentheses, tells REGEXEXTRACT to return only that part instead of the whole match. Google's help says that with several groups, each result goes in its own column. That lets one formula return a label-free number, or split a whole line into fields.

### One group: keep only the number

To pull a check number from `CHECK #1042`, match the label but capture only the digits:

`=REGEXEXTRACT(A2, "(?i)check\s*#?\s*(\d+)")`

The result is 1042, without "CHECK #". The same idea works for references, card endings and invoice numbers.

### Pattern table

The values below are made-up examples of statement text:

| Example text in A2 | Pattern | Result |
| --- | --- | --- |
| CHECK #1042 | `(?i)check\s*#?\s*(\d+)` | 1042 |
| ACH CREDIT ACME PAYROLL REF 88231 | `(?i)ref\W*(\w+)` | 88231 |
| POS PURCHASE CARD 4417 | `(?i)card\s*(\d{4})` | 4417 |
| ZELLE TO J SMITH CONF# A7K2Q9 | `(?i)conf#?\s*(\w+)` | A7K2Q9 |
| SQ *FLOUR SUPPLY CO 03/13 | `^(?:SQ \*)?(.*?)\s+\d{2}/\d{2}$` | FLOUR SUPPLY CO |

The last row uses `(?: )` again, a group that matches without being returned, and `.*?`, a lazy match that stops as early as it can. Together they drop the "SQ *" prefix and the date and keep the merchant name.

### Several groups: one line into columns

When an OCR or pasted line holds a whole transaction, three groups split it at once:

`=REGEXEXTRACT(A2, "^(\d{2}/\d{2})\s+(.*?)\s+(-?[\d,]+\.\d{2})$")`

For the example line `03/14 COFFEE HOUSE MAIN ST 6.75`, the formula fills three cells: 03/14, COFFEE HOUSE MAIN ST and 6.75. Leave the two cells to the right empty, or the results have nowhere to go.

## Why does REGEXEXTRACT return #N/A or the wrong piece?

REGEXEXTRACT returns an #N/A error when the pattern finds nothing in the cell, and the wrong piece when the pattern is too loose and matches something earlier. Most fixes are anchoring the pattern, being more specific about digits, or handling rows that legitimately have no match.

Common causes:

- **No match on some rows.** Not every description has a check number. Wrap the formula: `=IFNA(REGEXEXTRACT(A2, "..."), "")` returns a blank instead of an error.
- **A number cell as input.** The function wants text. If A2 holds a real number, use `REGEXEXTRACT(TEXT(A2, "0.00"), "...")` or `A2&""`.
- **Greedy matching.** `.*` grabs as much as it can, so `(.*)\s\d` captures nearly the whole line. Use `.*?` when you want the shortest match.
- **Unsupported syntax.** RE2 has no lookahead, lookbehind or backreferences, per its syntax page. Patterns copied from other tools that use `(?<=` or `\1` fail. Rewrite them with a capture group.
- **Unescaped symbols.** `.`, `$`, `(`, `)` and `*` have special meanings. To match them literally, put a backslash in front.
- **Quotes in the pattern.** A double quote inside a formula string must be typed twice.

Test a pattern on five or six real rows before filling it down a column of thousands. Sort the results and scan the top and bottom; bad matches tend to cluster there.

## Turning REGEXEXTRACT output into real numbers and dates

REGEXEXTRACT always returns text, so an extracted amount will not add up and an extracted date will not sort by calendar. Wrap the formula in VALUE for amounts and DATEVALUE for dates. The REGEXEXTRACT help page itself recommends VALUE when you need a number.

Examples:

- Amount: `=VALUE(REGEXEXTRACT(A2, "-?[\d,]+\.\d{2}$"))`
- Full date: `=DATEVALUE(REGEXEXTRACT(A2, "\d{1,2}/\d{1,2}/\d{4}"))`, then format the column as a date.
- Month and day only: add the year from the statement period, such as `=DATEVALUE(REGEXEXTRACT(A2, "^\d{2}/\d{2}") & "/2026")`.

The year trick needs care for statements that run across January. A December line on a January statement belongs to the year before, so check the first and last rows.

Once the columns are numeric, you can copy them and paste them back as values so the sheet no longer depends on the formulas. That matters before you hand the file to someone else, for example when you [download Google Sheets as Excel](/blog/download-google-sheets-as-excel) for an accountant. Pasted values travel cleanly; long formula chains are one more thing to check after the export.

## When is a REGEXEXTRACT formula the wrong tool?

Skip REGEXEXTRACT when a simpler function does the job or when the text is too unreliable for any pattern. A fixed separator calls for SPLIT. A fixed position calls for LEFT, MID or RIGHT. Badly recognised scan text calls for a better source, not a cleverer pattern.

Pick by the shape of the text:

- **Every field separated by the same character**, such as a pipe or tab: SPLIT is shorter and easier for the next person to read.
- **A code always in the first six characters**: LEFT(A2, 6) does it without a pattern.
- **True or false questions**, such as "does this line mention PAYROLL?": REGEXMATCH returns TRUE or FALSE directly.
- **OCR text full of misread characters**: a pattern that expects digits fails when a zero came back as the letter O. Rescan or retake the image; the guide to [using your phone as a document scanner](/blog/use-phone-as-document-scanner) covers getting a sharper page.

The best fix of all is not to need extraction. When a statement arrives as a digital PDF, a converter can place dates and amounts in their own columns from the start. FastBankConvert is being built to do that, and it is getting ready to open; [sign up through Get started](/get-started) to hear when trial conversions begin. REGEXEXTRACT then stays useful for what is left inside the description, such as references and check numbers.