## 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. Let's get started.

**Table of Contents**

## 1. Extract numbers from a column

The steps below demonstrate how to select only numbers from cell range B3:B12 manually.

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

- Select cell range B3:B12 with your mouse.
- Press function key F5 on your keyboard.

- A dialog box appears, press with left mouse button on "Special..." button.
- 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".
- Make sure checkbox "Numbers" is selected only, see image above.
- Press with left mouse button on OK button.

- The selection changes to only selecting numbers, see image above.
- Copy these cells. (CTRL + c)
- Select a destination cell with your mouse.
- 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.

## 2. Extract numbers from a column - Excel 365

Excel 365 formula in cell D3:

### 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)

becomes

ISNUMBER({"VV"; "DD"; 5; 8; 5; "VV"; "RR"; "TT"; "DD"; 9})

and returns

{FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; 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))

becomes

FILTER({"VV"; "DD"; 5; 8; 5; "VV"; "RR"; "TT"; "DD"; 9}, {FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE})

and returns {5; 8; 5; 9}.

## 3. Extract numbers from a column - earlier Excel versions

**Question**: I want to extract all numeric values into a new column?

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)

becomes

ISNUMBER({"VV"; "DD"; 5; 8; 5; "VV"; "RR"; "TT"; "DD"; 9})

and returns

{FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; 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, "")

becomes

IF({FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}, ROW($B$3:$B$12)-MIN(ROW($B$3:$B$12))+1, "")

becomes

IF({FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}, {1;2;3;4;5;6;7;8;9;10}, "")

and returns

{""; ""; 3; 4; 5; ""; ""; ""; ""; 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))

becomes

SMALL({""; ""; 3; 4; 5; ""; ""; ""; ""; 10}, ROWS(D2:$D$2))

The ROWS function returns the number of rows in a cell reference, in this case, the cell reference expands when the cell is copied to cells below.

SMALL({""; ""; 3; 4; 5; ""; ""; ""; ""; 10}, ROWS(D2:$D$2))

becomes

SMALL({""; ""; 3; 4; 5; ""; ""; ""; ""; 10}, 1)

and 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)))

becomes

INDEX($B$3:$B$12, 3)

and returns 5 in cell D3.

### Extract category

The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

### Excel categories

### 2 Responses to “Extract numbers from a column”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hey,

I need to find out the number (Ex_1.2) which is in Column and Range(0 1 2 3 4 5)...So at the output I get that Your Number is in between (1 & 2). Is it possible for you to create such custom code.

Thanks in advance.

i have number

cell a

123/7778123456

7778123457ram bahadur

ipc1237778123458

i need only started from 7778 with six character like

7778123456

7778123457

7778123458