Extract numbers from a column

I this article I will show you how to get numerical values from a cell range manually and using an array formula. The way this works is that the methods demonstrated here extracts cell values containing only numbers. For example, if a cell value contains 55 then this value is a numerical value. A cell value containing "car" is a text value and is not extracted. If a cell value contains both digits and another characters then that value is identified as a text value even though it contains numbers.

This means that these techniques does not involve extracting digits from values containing both digits and other characters like upper and lower case letters among others. The following links takes you to articles that show how to extract numbers from cells containing both numbers and other characters:

• How to use the N function - example 3
• How to extract numbers from a cell value

This article shows how to remove numerical values or digits from a value:

• How to remove numbers from a cell value

Let's get started.

1. Extract numbers from a column

The steps below demonstrate how to select only numbers from cell range B3:B12 manually. This means that you will be shown how to select cells containing only numbers and then copy the selection to a new cell range. Excel

You don't need to select all cells containing numbers one by one, this trick will save you a lot of time.

1. Select cell range B3:B12 with your mouse.
2. Press function key F5 on your keyboard.
   
3. A dialog box appears, press with left mouse button on "Special..." button.
4. Another dialog box shows up, press with left mouse button on "Constants".
   Note, if your selection contains formula then press with left mouse button on "Formulas".

   

5. Make sure checkbox "Numbers" is selected only, see image above.
6. Press with left mouse button on OK button.
   
7. The selection changes to only selecting numbers, see image above.
8. Copy these cells. (CTRL + c)
9. Select a destination cell with your mouse.
10. Paste selection. (Ctrl + v)

The image below demonstrates an array formula that extracts numbers only, this can be useful in a dashboard or interactive worksheets.

The "Go to Special" dialog box in Microsoft Excel is a useful tool that allows you to quickly select specific types of cells or data within a worksheet. Here are some of the main things you can do with the "Go to Special" dialog box:

1. Select Specific Cell Types:
   • Blanks - Selects all the blank cells in the selected range.
   • Constants - Selects all the cells containing constant values (not formulas).
   • Formulas - Selects all the cells containing formulas.
   • Numbers - Selects all the cells containing numeric values.
   • Text - Selects all the cells containing text.
   • Errors - Selects all the cells containing error values (e.g., #DIV/0!, #N/A, etc.).
2. Select Unique Values:
   • Unique - Selects all the unique values in the selected range.
   • Duplicate - Selects all the duplicate values in the selected range.
3. Select Conditional Formatting:
   • Conditional Formats - Selects all the cells with conditional formatting applied.
4. Select Data Validation:
   • Data Validation - Selects all the cells with data validation rules applied.
5. Select Hyperlinks:
   • Hyperlinks - Selects all the cells containing hyperlinks.
6. Select Commented Cells:
   • Comments - Selects all the cells with comments.

The "Go to Special" dialog box can be particularly useful when you need to quickly identify and work with specific types of cells or data within a large worksheet. It can save time and make your data analysis and manipulation tasks more efficient.

Back to top

2. Extract numbers from a column - Excel 365

This section shows how to list cells containing numbers and only numbers. The image above shows source values in cell range B3:B12, they are VV, DD, 5, 8, 5, VV, RR, TT, DD, and 9. The formula in cell D3 lists values from B3:B12 that are identified as numbers meaning Excel interprets the value to be of a data type equal to a number.

Excel 365 formula in cell D3:

The formula in cell D3 returns 5, 8, 5, and 9 which are all numbers obviously. The output is an array that spills to cells below as far as needed. The formula utilizes two functions:

• ISNUMBER function which is a function that works in most Excel versions.
• FILTER function is a fairly new function available to only Excel 365 subscribers. This function is a dynamic array formula meaning it may return an array that spills top cells below.

Explaining formula

Step 1 - Identify numbers

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.

Function syntax: ISNUMBER(value)

ISNUMBER(B3:B12)

returns {FALSE; FALSE; TRUE; ... ; TRUE}.

Step 2 - Filter numbers

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B12, ISNUMBER(B3:B12))

returns {5; 8; 5; 9}.

Back to top

3. Extract numbers from a column - earlier Excel versions

If you have both letters and digits in a cell then read this article:
How to extract numbers from a cell value

Array formula in C2:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell D3

Step 1 - Check if a value is a number

The ISNUMBER function returns TRUE if value is a number.

ISNUMBER($B$3:$B$12)

returns {FALSE; FALSE; TRUE; ... ; TRUE}

Step 2 - Convert TRUE to corresponding row number

The IF function returns corresponding row number if the logical test is TRUE and nothing "" if the logical test is FALSE.

IF(ISNUMBER($B$3:$B$12), ROW($B$3:$B$12)-MIN(ROW($B$3:$B$12))+1, "")

returns {""; ""; 3; ... ; 10}

Step 3 - Return k-th smallest row number

The SMALL function makes sure that a new value is returned in each cell.

SMALL(IF(ISNUMBER($B$3:$B$12), ROW($B$3:$B$12)-MIN(ROW($B$3:$B$12))+1, ""), ROWS(D2:$D$2))

returns 3.

Step 4 - Return value

The INDEX function returns a value based on a row and column number.

INDEX($B$3:$B$12, SMALL(IF(ISNUMBER($B$3:$B$12), ROW($B$3:$B$12)-MIN(ROW($B$3:$B$12))+1, ""), ROWS(D2:$D$2)))

returns 5 in cell D3.

Back to top

Back to top

Extract category

More than 1300 Excel formulas

Excel categories

Comments (2)