Extract Numbers in Excel: From a Column or From Text

by

Two different jobs share this name: pulling the number cells out of a mixed column, and pulling the digits out of text like INV-2041A. Here are formulas and tools for both.

Number cellsFILTER + ISNUMBER, Go To Special, Power Query
Digits in textTEXTJOIN + MID, REGEXEXTRACT
Works inAll versions (dynamic formulas: 365)

The data

Column with a mix of text and numbers

Part 1: Keep only the number cells

FILTER and ISNUMBER (Excel 365)

ISNUMBER returns TRUE for numbers. FILTER keeps those rows:

=FILTER(B3:B14,ISNUMBER(B3:B14))
ISNUMBER returning TRUE for numbers and FALSE for text
FILTER and ISNUMBER returning only the numbers

IF and ISNUMBER (any version)

=IF(ISNUMBER(B3),B3,"")
IF and ISNUMBER returning numbers and blanks

Numbers stored as text (left-aligned, with a green triangle) aren’t counted by ISNUMBER. To include them, use =IFERROR(B3*1,""), which converts them, but also turns blank cells into 0.

IFERROR and multiplication by 1 extracting numbers

Go To Special (no formula)

  1. Select the column and press Ctrl + G, then Special.
  2. Choose Constants, tick only Numbers, and click OK.
  3. Copy (Ctrl + C) and paste where you want the list.
Go To Special dialog with Constants and Numbers ticked
Numbers copied out of the column

Power Query

  1. Load the column with Data → From Table/Range.
  2. Click the type icon in the header and choose Decimal Number. Text becomes Error.
  3. Right-click the header → Remove Errors, then Close & Load.
Changing the column type to Decimal Number in Power Query
Remove Errors in Power Query
Table of numbers loaded back into Excel

Part 2: Pull the digits out of text

For values like INV-2041A or Room 12B:

Goal Formula (Excel 365) Result for INV-2041A
All digits, joined =--TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")) 2041
First run of digits =--REGEXEXTRACT(A2,"\d+") 2041
Every number as a list =REGEXEXTRACT(A2,"\d+",1) Each number in its own cell

The -- turns the text result into a real number. Leave it off to keep leading zeros, such as in PIN codes.

In older Excel, use Power Query: Add Column → Custom Column with Text.Select([Column1],{"0".."9"}). See remove or extract special characters.

Things to know

  • REGEXEXTRACT is in current Microsoft 365 only. TEXTJOIN with SEQUENCE works in Excel 2021 and 365.
  • Decimal points and minus signs are dropped by the digit-only methods. Use "-?\d+\.?\d*" with REGEXEXTRACT to keep them.
  • For a one-off clean-up, the list cleaner and text case converter tools can help prepare messy lists.

Related