텍스트에서 숫자 추출하기: 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가 되므로, 앞자리 0이 필요하면 변환하지 마세요. 천 단위 쉼표도 먼저 빼세요. Microsoft 문서에 따르면 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