COUNTMATCHES in Apple Numbers: Count Substrings, Overlaps and Regex

COUNTMATCHES gives the number of matches inside one source string. It is useful for estimating how many separators, tags, error tokens or repeated words appear within a cell, and for checking whether a particular substring appears at all. A cell can contain several matches; the result is a numeric count, not the matched text.

This is conceptually different from a function such as COUNTIF used across multiple cells. For example, if a single cell contains red-red-red, COUNTMATCHES with search string red returns 3 by its documented counting semantics; counting the number of cells that contain red is a different question.

Crucial detail: Apple explicitly counts overlapping occurrences as separate matches. Ordinary nonoverlapping search algorithms can therefore give the wrong answer. Official behaviour.

=COUNTMATCHES(source-string, search-string)

Position: 1

Official name
source-string

Required?
Yes

Documented type
Any value

Role
The source to search, normally a cell containing text.

Position: 2

Official name
search-string

Required?
Yes

Documented type
String value, also supports an Apple REGEX(...) expression

Role
The substring or regex pattern to count.

No optional start position, case flag, ignore-empty switch, or whole-word mode appears in Apple’s COUNTMATCHES signature. COUNTMATCHES is case-insensitive by default; use the separate REGEX function for custom matching and case sensitivity. Apple’s documentation does not fully specify how nonstring source values are coerced, nor does it define the result for an empty search string. This article therefore uses text inputs and nonempty search terms instead of inventing those edge-case rules.

Locale and Numbers table references

In a table, use =COUNTMATCHES(A2,B2) with text in A2 and the search substring in B2. Apple’s formula-reference guide explains how to reference other tables. Per the functions overview, when the device uses a comma as decimal separator, type a semicolon between function arguments: =COUNTMATCHES(A2;B2). For nested REGEX with a case flag, both argument separators also change: =COUNTMATCHES(A2;REGEX("item[0-9]";FALSE)). The punctuation within the string literal is not a separator.

Create a Numbers table with columns A: Source, B: Search for, and C: Match count. Put the following data in A2:B7. Enter =COUNTMATCHES(A2,B2) into C2 and fill down. Results below are mathematical/documentation-derived expectations, not native execution observations.

Row: 2

A: Source text
red-red-red

B: Search term
red

Formula in C
=COUNTMATCHES(A2,B2)

Expected count
3

Why
Three occurrences separated by hyphens.

Row: 3

A: Source text
asasa

B: Search term
asa

Formula in C
=COUNTMATCHES(A3,B3)

Expected count
2

Why
Two matches overlap at the middle a.

Row: 4

A: Source text
Apple apple APPLE

B: Search term
apple

Formula in C
=COUNTMATCHES(A4,B4)

Expected count
3

Why
Default search ignores case.

Row: 5

A: Source text
aaaa

B: Search term
aa

Formula in C
=COUNTMATCHES(A5,B5)

Expected count
3

Why
Positions 1–2, 2–3, and 3–4 all match.

Row: 6

A: Source text
blue

B: Search term
red

Formula in C
=COUNTMATCHES(A6,B6)

Expected count
0

Why
No occurrence.

Row: 7

A: Source text
A|B|C|D

B: Search term
|

Formula in C
=COUNTMATCHES(A7,B7)

Expected count
3

Why
Count delimiter characters.

All terms must be stored as text for this reproducible lesson. Avoid row-specific worksheet names or cross-table references unless you deliberately change the formula and verify the selected table. After filling down, spot-check C3 and C5 first: both are cases where a naïve nonoverlapping match count would undercount.

Apple explicitly supports REGEX inside COUNTMATCHES. Its own example is:

=COUNTMATCHES("To count all words, use regex.", REGEX("\w+"))

The documented result is 6 (the word-like tokens are To, count, all, words, use, regex). Independent Python regex tokenization also yields 6 for that exact sample. Apple says REGEX patterns must conform to the ICU standard. Do not assume this simplistic \w+ rule perfectly counts human-language words with hyphens, apostrophes, scripts or emoji. Determine what a word means in your data, then test edge cases on your Numbers version. Apple REGEX.

For text already in A2, Apple demonstrates the general structure =COUNTMATCHES(A2,REGEX("\w+")). Only apply this sample to ordinary word-separated test cases until the behaviour for your language and punctuation has been tested.

Apple’s REGEX syntax is:

REGEX(regular-expression-string, case-sensitive)

The second parameter is optional; Apple specifies TRUE or omitted means case-sensitive, while FALSE means ignore case. This is deliberately the opposite of assuming the parent COUNTMATCHES default overrides REGEX’s own flags. Apple provides these two official examples:

=COUNTMATCHES("Item1, item2, item3", REGEX("item[0-9]"))        → 2
=COUNTMATCHES("Item1, item2, item3", REGEX("item[0-9]",FALSE))  → 3

The first counts only lower-case item2 and item3; the second also counts Item1. These are Apple-documented outputs, not our app observations. The REGEX function also supplies other matching logic; its pattern must be valid ICU syntax. Official REGEX options.

• You expected 1 but got 2 or 3: Check for overlapping matches. Apple’s asasa example is designed to make this behaviour clear.
• Capitalization changed the count: Plain substring matching is case-insensitive. To demand case sensitivity, explicitly wrap the pattern in REGEX; omission of REGEX’s case-sensitive flag means TRUE.
• A regex returns unexpected counts: Verify ICU pattern syntax and whether you passed a true REGEX expression instead of an ordinary string. Apple warns that concatenating REGEX with plain strings can make it a plain string, losing regex status.
• The result is zero: Check for typographical differences, punctuation, different dash characters or unanticipated whitespace. The sample with blue and red is expected to have no matches.
• An empty or malformed pattern gives an error or surprising number: Apple’s COUNTMATCHES article does not specify empty-pattern semantics, and this guide does not claim a particular outcome. Use a nonempty pattern; run native tests before adding an error-handling promise.
• Your source contains a number, date or Boolean: Apple calls the source parameter any value but the precise string conversion semantics are not provided on its COUNTMATCHES page. Normalize the source to known text before comparing production results.
• Results disagree by device or version: Record the installed Numbers version, platform, locale, exact cell contents and formula. Do not treat documentation version 15.4 as proof of every older release or parity across Mac, iPhone, iPad, and web.

Apple officially lists COUNTMATCHES, its overlapping substring behaviour, and the REGEX-based customization. Microsoft’s current Excel text-function list does not list a function by this name. That list omission is not a proof about every historical add-in or extension, but it is a strong portability warning: do not paste COUNTMATCHES directly into Excel and promise it will work. Likewise, Google’s official Sheets function list shows T and various text functions, not an identical guaranteed COUNTMATCHES contract; consult Sheets documentation for an adapted approach.

In Excel, a length-minus-substitution technique can count nonoverlapping occurrences for suitably chosen substrings, but it cannot be treated as a drop-in equivalent to Apple’s overlap-counting function. For example, aaaa contains three overlapping aa matches; repeatedly removing aa removes only two nonoverlapping pairs. This distinction matters when designing a cross-software comparison page.

The Apple COUNTMATCHES guide, REGEX guide, functions overview, and formula references were checked 2026-10-12. The checked help edition was 15.4, with minimum release and cross-platform parity not established by these pages.

• CHAR — continue with a related Apple Numbers calculation.
• CODE — continue with a related Apple Numbers calculation.
• CLEAN — continue with a related Apple Numbers calculation.
• T — continue with a related Apple Numbers calculation.
• REPT — continue with a related Apple Numbers calculation.

The linked official function documentation was reviewed on October 12, 2026. Apple’s Formulas and Functions Help 15.4 identifies the documentation edition, not a minimum Numbers release or a guarantee of historical platform parity. Selected worked examples were checked using independent mathematical or text-processing models; results are expected values, not observations from the spreadsheet application. Native Apple Numbers execution was not performed. Undocumented coercion, exact error messages and platform-specific behaviour remain unverified.

View reproducible example inputs and independent check results. This record is not a native spreadsheet test log.