## How to extract numbers from a cell value

*Article updated on March 25, 2018*

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value:

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.

If your cell contains more than a 1000 characters change this part of the formula $A$1:$A$1000 to perhaps $A$1:$A$2000 depending on how many characters you have.

### Explaining array formula in cell C3

The LEN function returns the number of characters in cell C3, so we can split each character into an array.

LEN(B3) returns 12.

The INDEX function allows us to build a cell reference with as many rows as there are characters in cell B3.

$A$1:INDEX($A$1:$A$1000, LEN(B3))

becomes

$A$1:INDEX($A$1:$A$1000,12) and returns $A$1:$A$12.

The ROW function converts the cell reference to an array

ROW($A$1:INDEX($A$1:$A$1000, LEN(B3)))

becomes

ROW($A$1:$A$12) and returns {1; 2; 3; ... 12}

The MID function splits each character in cell B3 into an array.

MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)

becomes

MID(B3, {1; 2; 3; ... 12}, 1) and returns {"1";"2";"3"; ... ;" "}

The TEXT function returns numerical values but leaves all other characters blank.

TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), "#;-#;0;")

TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), "#;-#;0;")

becomes

TEXT({"1";"2";"3";" ";"B";"O";"A";" ";"2";"1";"4";" "}, "#;-#;0;") and returns {"1"; "2"; "3"; ""; ""; ""; ""; ""; "2"; "1"; "4"; ""}.

The TEXTJOIN function introduced in Excel 2016 allows you to easily concatenate an array for values. In this case, it also ignores blank values in the array.

TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), "#;-#;0;"))

becomes

TEXTJOIN(, 1, {"1"; "2"; "3"; ""; ""; ""; ""; ""; "2"; "1"; "4"; ""}) and returns 123214 in cell C3.

### Download Excel *.xlsx file

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article