## How to extract numbers from a cell value

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

#### Step 1 - Count characters

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

LEN(B3)

becomes

LEN("123 BOA 214")

returns 12.

#### Step 2 - Build cell reference

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.

#### Step 3 - Create numbers based on row number of each cell in cell reference

The ROW function converts the cell reference to an array of numbers corresponding to the of each cell.

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

becomes

ROW($A$1:$A$12)

and returns {1; 2; 3; ... 12}.

#### Step 4 - Create an array

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"; ... ;" "}

#### Step 5 - Filter out text letters

The TEXT function returns numerical values but leaves all other characters blank if you use the following pattern in the format_text argument: "#;-#;0;"

TEXT(*value*, *format_text*)

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"; ""}.

#### Step 6 - Join numbers

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.

### Get Excel *.xlsx file

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 […]

I this article I will show you how to get numerical values from a cell range manually and using an […]

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 […]

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 “How to extract numbers from a cell value”

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

similar Matrix solution but i think it's easier to explain, even if it feels inefficient: It Uses the "new" CONCAT instead of TEXTJOIN (Formula translated to English with VBA. It might work...)

`=CONCAT(IFERROR(MID(A1,ROW(1:1000),1)*1,""))*1`

and with dynamic lenght

`=CONCAT(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,""))*1`

A. Nony Mous

Thank you for your comment.

=CONCAT(IFERROR(MID(A1,ROW(1:1000),1)*1,""))*1

Your formula is a better, shorter and easier to understand.