Google Sheets will not run a regex on a date, and the obvious fix hands your pattern 46213

You have a column of dates. You want the rows from 2026. You write the obvious thing:

=REGEXMATCH(A2, "^2026")

The cell returns #VALUE!. Hover over it, and Google tells you something it has no obligation to tell you, and which is the most useful sentence in this entire article:

Function REGEXMATCH parameter 1 expects text values. But ‘46213’ is a number and cannot be coerced to a text.

46213. That is your date. Not 2026-07-10 — that is a costume the column is wearing. Underneath, a date in Google Sheets is a count of days from 30 December 1899, and today’s is 46213.

What we ran

Thirty-eight formulas, in a live Google Sheet. Dates, times, percentages, currency, booleans, employee ids with leading zeros, a sixteen-digit invoice number, one third, and the number 5.

We expected to find that the regex functions quietly stringify a number and match against the digits. They do not. They refuse.

Sheets refuses, and that is the good news

Every one of these returns #VALUE!:

=REGEXMATCH(DATE(2026,7,10), "2026")     the date column
=REGEXMATCH(50%, "50")                   the percentage column
=REGEXMATCH(1240, "^\$")                 the currency column
=REGEXMATCH(007, "^0")                   the employee id
=REGEXMATCH(TRUE, "TRUE")                a checkbox
=REGEXMATCH(5, "\.")                     the number five

REGEXEXTRACT and REGEXREPLACE refuse the same way. There is no coercion, no guessing, no partial credit. If the parameter is not text, the function stops.

Readers of this site will notice that this is unusual. Most of what we measure is Sheets returning a wrong answer in silence. Here it declines to answer at all, and it names the value it was handed while doing so. If it stopped here, this would be a page about a well-designed error message.

Then you fix it

The repair is one you have already thought of, and it is in every answer to this question you will find. Make the value text by concatenating an empty string:

=REGEXMATCH(A2 & "", "^2026")

No more #VALUE!. The formula runs. It returns:

FALSE

For every row. In 2026, in 2025, in any year. Because A2 & "" does turn the value into text — the text 46213. There is no 2026 anywhere in it, and there never was. The refusal has become a wrong answer with no warning, and it is now wearing the same face as an honest FALSE.

What & "" actually hands your pattern

We ran each one. The left column is what the cell displays. The right column is the string the regex receives.

the cell shows	the pattern gets
2026-07-10	46213
50%	0.5
$1,240.00	1240
007	7
5.00	5
09:30	0.395833333333333
TRUE	TRUE

Read that column again with a pattern in mind.

=REGEXMATCH(pct & "", "50") is FALSE on a cell that says fifty percent. =REGEXMATCH(amount & "", ",") is FALSE on a cell that says $1,240.00, because the comma belongs to the format. =REGEXMATCH(id & "", "^0") is FALSE on the employee whose badge says 007. And =REGEXMATCH(price & "", "\.") is FALSE on a cell displaying 5.00, because the two zeros after the point are a format instruction, not digits.

Then there is the one that is TRUE by accident:

=REGEXMATCH(TIME(9,30,0) & "", "9")   →   TRUE

Half past nine in the morning is 0.395833333333333 of a day, and that happens to contain a 9. The formula is asking whether the time contains a nine. It says yes. It is right, for reasons that have nothing to do with nine o’clock, and it will keep being right until someone changes the meeting to half past ten.

How much of a number does a pattern get to see

Two small mercies and one trap, since we had the sheet open.

=REGEXEXTRACT(1/3 & "", ".+")           →   0.333333333333333
=REGEXEXTRACT(0.0000001 & "", ".+")     →   0.0000001
=REGEXMATCH("", "^$")                   →   TRUE

One third arrives with fifteen digits after the point, however many the column is wide enough to show. If you are matching ^0\.33$ against a computed ratio, you are matching against a string with thirteen more characters in it than you think.

A very small number stays in decimal notation rather than turning into 1E-07, which is a mercy: a pattern like ^\d+\.\d+$ survives it. Do not assume that generalises to every magnitude without testing yours.

And an empty cell is already text. REGEXMATCH does not refuse it, so ^$ is a legitimate way to ask whether a cell is blank — one of the few places a regex is the simple answer.

The one that destroys data

=REGEXEXTRACT(1234567890123456 & "", ".+")   →   1234567890123450

Look at the last digit.

A sixteen-digit number — an invoice reference, a bank code, a card number, an order id from a system that had no reason to expect a spreadsheet — loses its sixteenth digit on the way into a string. Google Sheets carries fifteen significant digits, and the sixteenth becomes a zero.

=REGEXMATCH(1234567890123456 & "", "3456$")   →   FALSE

The number does not end in what it ends in. If a column of these was ever typed as numbers rather than text, the damage is already done, and it happened before any regex was involved. Every long id you paste into a spreadsheet should be text before it is anything else.

What actually works

TEXT(). It takes the value and the format, and it produces the string a person would read.

=REGEXMATCH(TEXT(A2, "yyyy-mm-dd"), "^2026")            →   TRUE
=REGEXMATCH(TEXT(B2, "hh:mm"), "^09:30$")               →   TRUE
=REGEXMATCH(TEXT(C2, "$#,##0.00"), "^\$1,240\.00$")     →   TRUE

All three measured, all three TRUE. & "" asks the value what it is. TEXT() asks it what it looks like. Those are different questions, and a regex is a question about appearance.

What to do on Monday

When a regex says #VALUE!, read the message. It contains the number your column is actually made of. That number is the entire explanation, and it will be a five-digit integer if you are looking at a date.

Never repair #VALUE! with & "". It silences the error without answering the question. Use TEXT(value, format) with the format you want to match against, and now the pattern and the cell agree about what is being matched.

Type long ids as text before they arrive. Format the column as plain text first, or prefix with an apostrophe. Once a sixteen-digit reference has been stored as a number, no formula can get the sixteenth digit back, because it is not there.

Ask whether you need a regex at all. For dates, YEAR(A2) = 2026 is a comparison between two numbers, which is what the data already is. Reaching for text-matching on a numeric column is how you end up here.

Every formula quoted above was executed in a live Google Sheet before this was published, and the values are the values the cells returned — including the error message, which is Google’s own wording. The full run, with timestamps, is on the evidence page.