从文本中提取数字：Excel 与 Google Sheets

快速解答

在 Google Sheets 里用 REGEXEXTRACT 取出第一个数字，需要真正的数值时再套 VALUE。不要随便在模式前加可选负号：对 SKU-103 costs $12.50，那种模式返回了 -103，对 INV-2026-11 返回了 -2026。更稳妥的写法只在单元格开头才允许负号，该式对上述两例分别返回 103 和 2026，对 -3.2C 仍返回 -3.2。

=REGEXEXTRACT(A2,"^-?[0-9]+(?:\.[0-9]+)?|[0-9]+(?:\.[0-9]+)?")

检查要点

如果模式里用了捕获组，REGEXEXTRACT 只返回第一个组。把小数部分放进括号后，12.5 units 得到的是 .5，而不是 12.5。需要完整数字时用非捕获组。用 REGEXREPLACE 删掉所有非数字和小数点时，SKU 那一行变成了 10312.50，two numbers 10 and 20 变成了 1020，两段数字会被粘在一起。

边界与兼容性

VALUE("$12.50") 在本测试中失败，VALUE("12.50") 返回 12.5。先去掉货币符号再转换。0042 提取后仍是文本，VALUE 之后变成 42，若前导零有意义就不要转换。千分位逗号要先去掉。Excel 365 据微软文档已有 REGEXEXTRACT，本次未在桌面 Excel 中复测；旧版 Excel 需改用 Flash Fill 或逐字符公式，并先检查含两个数字的单元格。

完整英文示例、公式与作者备注

Quick answer: In Google Sheets, use REGEXEXTRACT to pull the first number, then VALUE if you need it as a true number. Do not give the pattern a leading -? unless the cell itself starts with a minus, and do not strip every non-digit from a cell that contains more than one number.

=REGEXEXTRACT(A2,"^-?[0-9]+(?:\.[0-9]+)?|[0-9]+(?:\.[0-9]+)?")

In this test, that formula returned 103 from SKU-103 costs $12.50, -3.2 from -3.2C, and 2026 from INV-2026-11. Wrap it in VALUE(...) when you want to add or compare the result as a number: 0042 stayed text after the extract and became 42 after VALUE.

Tested in: Live formulas in Google Sheets, November 12, 2026. Microsoft documents REGEXEXTRACT and REGEXREPLACE for Excel 365; those Excel functions were not run in a desktop workbook on this date. Older Excel does not have them.

Which versions this applies to

The REGEX formulas below were tested in Google Sheets. Excel 365 added REGEXEXTRACT and REGEXREPLACE, per Microsoft, but this article does not treat those Excel functions as tested. For older Excel, use Flash Fill, a helper column, or the digit-by-digit TEXTJOIN method shown later, and check the result by hand. VALUE exists in both products.

First, decide which number you want

The sample cell SKU-103 costs $12.50 contains two numbers. Which one you need changes the pattern.

The safer first-number formula above returned 103, the first run of digits. The last run of digits in that cell is the price:

=REGEXEXTRACT(A2,"[0-9]+(?:\.[0-9]+)?$")

That returned 12.50 as text. VALUE("12.50") in the same file returned 12.5. VALUE("$12.50") returned #VALUE! (shown as an error in the test sheet). If a currency symbol is still attached, strip it before VALUE, or extract only the digits and the decimal point.

A cell that is already a bare number does not need REGEX. VALUE("12") is unnecessary; the cell 12 was already 12. VALUE("12.5 units") failed, because of the trailing text. Extract first, then convert.

The hyphen in a SKU is not a minus sign

This is the mistake I made on the first pass, and the formula looked reasonable.

=REGEXEXTRACT(A2,"-?[0-9]+(?:\.[0-9]+)?")

On SKU-103 costs $12.50 that pattern returned -103. On INV-2026-11 it returned -2026. The optional minus (-?) will happily take the hyphen that sits just before a product number or a year.

The same pattern is the right one when the cell actually starts with a negative quantity: on -3.2C it returned -3.2, which is what you want.

The alternation used at the top of this article tries a leading signed number only at the start of the cell (^-?[0-9]+...) and otherwise takes an unsigned run of digits. In this file that returned 103, -3.2, and 2026 from those three cells, which is the split I wanted.

Parentheses change what REGEXEXTRACT returns

Google Sheets REGEXEXTRACT returns the first capturing group when the pattern contains one. A pattern written as -?[0-9]+(\.[0-9]+)? looks like “a number with an optional decimal,” but the parentheses make the decimal the group.

In this file, that capturing pattern returned .5 from 12.5 units, .2 from -3.2C, and .6 from temp is 98.6F today, instead of 12.5, -3.2, and 98.6. On SKU-103 costs $12.50 it returned a blank, because the first match was 103 and the optional group never fired.

If you want the whole number, use a non-capturing group: (?:\.[0-9]+)?. If you wrap the part you want in ordinary parentheses on purpose, be sure that is the only group, and that it is the piece you actually want to keep.

Do not smash every non-digit together

REGEXREPLACE(A2,"[^0-9.]","") on SKU-103 costs $12.50 returned 10312.50. On two numbers 10 and 20 it returned 1020. On INV-2026-11 it returned 202611. You can add VALUE after that and get a completely wrong number with no error.

A digit-by-digit join does the same thing, and it also drops the decimal point because a lone . is not a number:

=ARRAYFORMULA(TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1),"")))

In this file that formula returned 1031250 from SKU-103 costs $12.50 and 125 from 12.5 units. It is a usable fallback when you have a single integer buried in letters and no regex, and it is the wrong tool for prices, SKUs with digits, or any cell with two numbers.

The ARRAYFORMULA wrapper was required in this Sheets file so SEQUENCE and MID walked the whole string. Without a test like that, I would not ship the formula.

Thousands separators, leading zeros, and plain VALUE

1,234.5 is a number with a grouping comma, not two numbers. VALUE(SUBSTITUTE(A7,",","")) returned 1234.5. Extracting the first [0-9]+ without removing the comma first stopped at 1.

The cell 0042 is text (ISTEXT was TRUE) even though it looks numeric. REGEXEXTRACT returned 0042, still text, which is what you want if those zeros are part of an ID. VALUE("0042") and VALUE of that extract both returned 42. Use VALUE only when you are ready to drop the leading zeros.

no digits had no match. REGEXEXTRACT would have been #N/A; the test wrapped it in IFERROR and showed none. That miss is #N/A, not #VALUE!, so IFNA would also work around a failed extract. FIND and SEARCH miss with #VALUE! instead; they are not interchangeable here.

A short Excel path without regex

If you are in an Excel version that does not have REGEXEXTRACT, Flash Fill will often pull a trailing or leading number after one or two examples. For a single integer inside text, the TEXTJOIN and MID pattern above is the usual formula approach; in Excel 365 it can use TEXTJOIN and SEQUENCE without ARRAYFORMULA. Check a cell that has a decimal point or a second number before you fill the column, because this method will glue digits together the same way it did in Sheets.

Microsoft documents REGEXEXTRACT for Excel 365. I have not run those Excel formulas in this project, so I am not quoting Excel REGEX results.

Common mistakes

Using -? in front of the first digit run and turning SKU-103 into a negative quantity.
Leaving a capturing group around the decimal part and getting .5 instead of 12.5.
Stripping every non-digit and adding the leftover characters together.
Running VALUE on a string that still has a $, or on a full sentence. VALUE("$12.50") failed in this file; VALUE("12.50") returned 12.5.
Converting an ID such as 0042 with VALUE and losing the zeros you still needed.

FAQ

How do I get the price, not the SKU number, from a sentence?

Anchor the extract at the end of the cell, or write a pattern that includes the currency symbol and then strip the symbol. In this test, [0-9]+(?:\.[0-9]+)?$ returned 12.50 from SKU-103 costs $12.50.

Why did VALUE return an error on a cell that looks like a price?

VALUE("$12.50") failed here. The dollar sign is enough to stop it. Extract the digits, or SUBSTITUTE the symbol out, then convert.

Can I extract both numbers from one cell?

REGEXEXTRACT returns the first match only. You can extract the first, then look at the remainder, or write a tighter pattern for the one you care about (the last number, or the one after $). I did not find a one-cell REGEXEXTRACT in this file that returned both 103 and 12.50 together as two usable numbers.

Will this work in Excel 2016?

Not the REGEXEXTRACT formulas. Use Flash Fill or a MID-based formula, and test a row that has two numbers before you trust the column.

Should I keep the result as text or convert it with VALUE?

Keep text when leading zeros matter, or when you are going to look the value up as a code. Use VALUE when you need to add, sort, or average it as a quantity. After VALUE, 0042 was 42 in this file.

Live-test findings

All extracts above come from one Google Sheets file dated November 12, 2026. A pattern with a leading -? signed SKU-103 and INV-2026-11 as negatives. The safer alternation returned 103, -3.2, 2026, 10, 0.99, and 0042 from the sample cells listed in this article. REGEXREPLACE to keep only digits and dots turned SKU-103 costs $12.50 into 10312.50 and two numbers 10 and 20 into 1020. A capturing group around the decimal part returned .5 from 12.5 units. VALUE("$12.50") failed; VALUE("12.50") returned 12.5.

Sources

Google Docs Editors Help: REGEXEXTRACT, REGEXREPLACE, VALUE: https://support.google.com/docs/answer/3098244, https://support.google.com/docs/answer/3098245, https://support.google.com/docs/answer/3094219
Microsoft Support: REGEXEXTRACT function (Excel 365): https://support.microsoft.com/en-us/office/regexextract-function-6b569b62-2fc0-4035-b385-ba0d5f861b00
Microsoft Support: VALUE function: https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d9983cf43