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, click "Special..." button.

Another dialog box shows up, click "Constants".
Note, if your selection contains formula then click on "Formulas".
 Make sure checkbox "Numbers" is selected only, see image above.

Click 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.
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 kth 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 [โฆ]
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 [โฆ]
Chirag asks: I want to separate numbers from the following text: Abc123bx45 as a result 123 and 45 should be [โฆ]
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 kth 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, [โฆ]
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