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)
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}.
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.
Extract category
What's on this page How to extract numbers from a cell value - Excel 2016 Sort and return unique distinct […]
Table of Contents Extract first word in cell value Extract the first word in cell - return warning if not […]
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