Excel REGEX functions return text, so the numbers you extract add up to zero

Why does SUM return zero after REGEXEXTRACT?

I created a simple test sheet so I could verify the results easily. I put three entries in column A, which were Order 100 shipped, Order 250 shipped, and Order 50 shipped. In column B, I added =REGEXEXTRACT(A1,"\d+") and dragged it down the rows. Column B displayed 100, 250, and 50, which should add up to 400.

I ran nine different Excel functions against that same column B. They gave conflicting results, and that inconsistency makes the issue hard to spot because no single check will tell you the data is broken.

The two COUNTIF rows are a good example, and this specific issue often wastes hours of troubleshooting. When I asked COUNTIF if the value 100 was in the column, it said yes. But when I asked it how many values were greater than 60, it returned zero. Looking for an exact match works across text and numbers, but logical comparisons do not, so your quick sanity check passes while the formula fails.

The SORT function shows another strange result. It returned 100, 250, and 50, which looks almost right and is easy to miss in a large dataset. When Excel sorts text, “100” comes before “250” and “50” because it evaluates the first character first. Microsoft’s own error checking page documentation warns that storing numbers as text can cause unexpected sorting behavior.

Why does this pass when you test it in one cell?

This mistake is easy to miss because a single-cell test actually works fine. When I tested =SUM(REGEXEXTRACT(A1,"\d+")) on one value, it returned 100 as expected. Passing a value directly into SUM converts it to a number, but reading that same value inside a cell range causes SUM to skip it.

Your initial test works, so you drag the formula down, and the issue only appears once the values sit inside the sheet. This is similar to the issues I covered in my piece on silent spreadsheet errors, where the sheet does not show an explicit error, but it quietly computes the wrong result.

Why does Excel not flag it the way it normally does?

Excel runs automatic background checks on your data and marks potential errors with a green triangle in the corner of the cell. One check looks specifically for numbers stored as text. This is why pasting data from a web page or PDF usually highlights the cells and asks if you want to convert them.

That background check is active by default. I made sure it was enabled on my machine before setting up two test cells side by side. In the first cell, I set the format to Text and typed 100 manually. In the second, I used =REGEXEXTRACT("x100","\d+"), which outputs the text string “100”. Both cells look identical, but Excel only flagged the manually typed cell and completely ignored the formula output.

This matches how Microsoft describes the feature. The documentation states the rule flags cells containing numbers stored as text, which usually happens during external data imports. From what I saw, it inspects the value sitting in a cell, not one a formula worked out. That design works fine until your formulas generate text numbers, which is what regex functions do constantly.

There is one small visual indicator you can look for. Both cells used Excel’s General formatting, which aligns plain text to the left and numbers to the right. The extracted text sits on the left side while true numbers sit on the right, and I use this alignment shift as a quick check when cleaning messy Excel data. It is hard to notice in narrow columns, so do not rely on it as your only check.

I use this two-function check on any column generated by a formula instead of direct input. It is faster than reviewing individual cells, and it follows the same verification approach I described in checking whether ChatGPT or Copilot got your Excel numbers right.

Is wrapping it in VALUE the real fix?

Microsoft suggests converting REGEXEXTRACT outputs using the VALUE function, which works cleanly for amounts and measurements. When I applied =VALUE(REGEXEXTRACT(A1,"\d+")) down the column, it summed to 400, returned a count of 3, and sorted correctly as 50, 100, 250.

Standard advice suggests using VALUE on every extracted column, but doing that on specific data types will cause problems. I ran tests on three common scenarios to see what happens.

It helps to understand why 18-digit numbers fail, because they alter your data without showing an error. Excel limits numbers to 15 significant digits of precision, as detailed in Excel’s specifications and limits. An 18-digit order number is an identifier rather than a quantity, so converting it to a number turns the final three digits into zeros without any warning.

What if you do not want to change the column?

If a column is only meant to be read by people, keeping it as text is fine, and you can handle the conversion inside your final sum formula. The formula =SUMPRODUCT(B1:B3*1) returned 400 from my text column, and =SUM(B1:B3*1) produced the exact same result. Multiplying the range by 1 converts the text to numbers during calculation without modifying the displayed values.

To convert an entire range at once, the cleanest approach is writing =MAP(A1:A3,LAMBDA(r,VALUE(REGEXEXTRACT(r,"\d+")))) in the top cell and letting it spill down. It output 100, 250, and 50 as numbers and summed to 400. This requires only one formula instead of maintaining dragged-down cells, which is the same job I was doing on invoice lines in automating invoice data entry in Excel.

Amounts and quantities: wrap them in VALUE. IDs, invoice numbers, SKUs and phone numbers: leave them as text, and keep the column they are matched against as text too.

Why does REGEXEXTRACT pull 2026 out of INV-2026-0042?

Incorrect data types will break your formulas, but flawed regex patterns are worse because they output plausible numbers that are completely wrong. A regular expression is a syntax used to describe text patterns, where \d matches a digit and + matches one or more digits. Combining them as \d+ matches a continuous sequence of digits.

That pattern finds any sequence of digits, starting with the first match it encounters. On INV-2026-0042, applying \d+ extracted 2026, and running this across an invoice column will repeat that mistake without triggering errors. You end up extracting the year instead of the invoice number, leaving you with a clean column of incorrect values.

To fix this, define the position of the text alongside its structure. The pattern \d+$ ties the match to the end of the string and successfully returned 0042. Using (\d{4})-(\d+) with the return mode set to 2 extracted both 2026 and 0042 into separate cells. Tying your pattern to surrounding characters works much better than using a plain \d+.

What does the “all matches” mode really do?

The REGEXEXTRACT function includes a third argument where 0 pulls the first match, 1 returns all matches in an array, and 2 returns capturing groups from the first match. People often pick mode 1 to pull multiple numbers from a single cell, and it works as expected for individual cells. On ref 55 and 66, using =REGEXEXTRACT(A4,"\d+",1) extracted 55 and 66 across two adjacent cells.

Point the same formula at a column and it quietly stops doing that. =REGEXEXTRACT(A1:A4,"\d+",1) across my four rows returned one value per row, and for the row holding both numbers it returned 55 and dropped 66. It did not error and it did not spill sideways. Mode 1 is a per-cell feature, and against a range it gives you the same thing mode 0 would.

There are two specific behaviors worth remembering here. The pattern \b causes issues when numbers are attached to units, which happens often in inventory exports, and using \d+ works better than writing a complex pattern. Also, case sensitivity defaults are inverted compared to standard Excel functions, which usually ignore case. Microsoft notes in REGEXTEST page that regex matches are case sensitive by default, so you must set the last argument to 1 to disable it.

If your regex pattern contains an actual syntax error, Excel will let you know. The formula =REGEXTEST("abc","[") returned #VALUE!, and trying to reference a non-existent capturing group like =REGEXREPLACE("a1","\d","<$1>") raised the same error. That is one scenario where Excel halts calculation properly.

What does a regex actually read inside a cell?

Changing a cell’s number format only affects its visual appearance, not the underlying value. Every analyst knows this rule, but regex functions introduce a new trap because they evaluate the underlying stored value instead of the formatted text on your screen.

I entered a date into a cell using =DATE(2026,9,17) and formatted it as yyyy-mm-dd, which displays as 2026-09-17. Testing it with =REGEXTEST(A7,"2026") returned FALSE. Running =REGEXEXTRACT(A7,"\d+") returned 46282, which is the underlying serial number Excel uses to track dates starting from 1900.

Phone numbers display this same behavior. I entered 3125550142 into a cell and applied the custom format (000) 000-0000, displaying it as (312) 555-0142. Trying to extract the area code using \(\d{3}\) returned #N/A because the stored value lacks parentheses. When I typed the same phone number as raw text with parentheses included, the pattern extracted (312) instantly.

You can resolve both issues by feeding the function the formatted text shown on screen instead of the stored value. Using =REGEXEXTRACT(TEXT(A7,"yyyy-mm-dd"),"\d{4}") returned 2026 as expected. The TEXT function formats the string as specified so the regex pattern can read it correctly. For extra spaces, running TRIM first allows the anchored pattern to match.

This behavior also makes regex a bad option for finding duplicate entries that differ only by formatting. Two cells can display identical text on screen while holding completely different underlying data, which leads to the same issues I outlined in removing duplicates in Excel without losing data.

Which version should you write for your column?

I do not choose formulas based on which one is the shortest. I base my choice on what the column is used for, because that determines whether the final values should be text or numbers.

What happens when the file leaves your PC?

Microsoft provides these regex functions in Excel for Microsoft 365 and Excel for Mac 365, but they are missing from standalone versions like Excel 2021 and Excel 2024. That difference becomes a problem when sharing files with clients.

An .xlsx file is an XML archive, so I opened my saved workbook file to inspect the stored formulas directly. The formulas were written with prefixes as _xlfn.REGEXTEST, _xlfn.REGEXEXTRACT, and _xlfn.REGEXREPLACE. Excel adds the _xlfn prefix to functions that are not supported by older file formats, causing unsupported versions to display that prefix and return #NAME?.

Before sharing a workbook, copy your extracted columns and paste them back as static values to strip out the formulas. This avoids compatibility issues like the ones discussed in trim references that only work in Microsoft 365, where an unsupported function converts a completed sheet into a column of calculation errors.

Before you send a workbook that uses these functions, paste the extracted column as values. Check who opens it, not just whether it works on your screen.