## 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. Lets get started, the steps below demonstrates 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 willl 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.

**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.

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

Sort and return unique distinct single digits from cell range

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

Extract table headers based on a condition

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

The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]

The LEFT function allows you to extract a string from a cell with a specific number of characters, however, if […]

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

The formula demonstrated above in cell range C3:C9 extracts the last word from adjacent cell in column B. =TRIM(RIGHT(SUBSTITUTE(B3, " […]

Extract k-th word in cell value

The formula displayed above in cell range D3:D9 extracts a word based its position in a cell value. For example, […]

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