テキストから数値を取り出す：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 からは 12.5 ではなく .5 が返りました。数値全体が欲しいときは非捕捉グループを使います。数字と小数点以外を REGEXREPLACE で消すと、SKU の行は 10312.50、two numbers 10 and 20 は 1020 になり、別々の数がくっつきます。

注意点と互換性

VALUE("$12.50") はこのテストでは失敗し、VALUE("12.50") は 12.5 を返しました。通貨記号は先に取り除きます。0042 は抽出後も文字列で、VALUE のあと 42 になるため、先頭ゼロが必要なら変換しないでください。桁区切りのコンマも先に除きます。Excel 365 には Microsoft の説明どおり 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