# How to extract numbers from a cell value

### What's on this page

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

How to extract numbers from a cell value.xlsx

## 2. Sort and return unique distinct single digits from cell range

This section demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 contains three numbers in each cell. The formula in cell E2 extracts all single digits from cell range B3:B6, however, sorted from small to large and only one instance of each digit.

Numbers 2 and 3 are not shown in cell E2 because they don't exist in cell range B3:B6. Note, it is not required to have three digits in each cell in cell range B3:B6. You can have as many as you want.

**Eddie asks:**

*Find and Sort*

* I have question, in range A1,A2,A3,A4 contain 097 494 861 575.*

*What is the formula in excel if the result that i want is 01456789*

This formula contains two functions that are only available for Excel subscribers. This formula will not work in earlier Excel versions.

Update 8/26/2021 - Smaller formula:

### Explaining formula in cell E2

#### Step 1 - Find search values in cell range

The SEARCH function allows you to search cell values and if a given text string is found a number is returned which represents the character position.

SEARCH(*find_text*, *within_text*, [*start_num*])

For example if 1 is returned the cell value begins with the text string.

The great thing about the SEARCH function is that it lets you search for multiple text strings, this makes the function return an array of numbers instead of a single number.

SEARCH({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, B3:B6)

becomes

SEARCH({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, {"097";494;861;575})

Note that the search strings are using the comma as a delimiting character and the the within_text argument uses semicolons as a delimiting character.

and returns

{1, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 3, #VALUE!, 2;#VALUE!, #VALUE!, #VALUE!, #VALUE!, 1, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 2;#VALUE!, 3, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 2, #VALUE!, 1, #VALUE!;#VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 1, #VALUE!, 2, #VALUE!, #VALUE!}

The comma is a column delimiting character and the semicolon is a row delimiting character, if I enter the above array in cell C3 the formula expands automatically to cell range C3:L6. This is called spilling and is a new Excel 365 feature.

The image above shows the find_text argument in row 2, the within_text in column B and the array in cell range C3:L6. Number 0 is found in the first value 097 in character position 1.

#### Step 2 - Identify non-error values

The ISNUMBER function lets you check if a cell value is a number, it returns the boolean value TRUE if a number and FALSE for everything else even error values.

ISNUMBER(SEARCH({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, B3:B6))

becomes

ISNUMBER({1, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 3, #VALUE!, 2;#VALUE!, #VALUE!, #VALUE!, #VALUE!, 1, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 2;#VALUE!, 3, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 2, #VALUE!, 1, #VALUE!;#VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 1, #VALUE!, 2, #VALUE!, #VALUE!})

and returns

{TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE}

This step is required, the MMULT function, which we will use in step 4, can't process error values.

#### Step 3 - Convert boolean values to their numerical equivalents

Boolean value TRUE is the same thing as 1 and FALSE is 0 (zero). It is easy to convert boolean values simply multiply with 1.

ISNUMBER(SEARCH({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, B3:B6))*1

becomes

{TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE}*1

and returns

{1, 0, 0, 0, 0, 0, 0, 1, 0, 1;0, 0, 0, 0, 1, 0, 0, 0, 0, 1;0, 1, 0, 0, 0, 0, 1, 0, 1, 0;0, 0, 0, 0, 0, 1, 0, 1, 0, 0}

#### Step 4 - Merge values column-wise using OR logic

The MMULT function is a capable of aplying OR logic for each column, the result is an array shown in row 7 in the image below.

MMULT(TRANSPOSE(ROW(B3:B6)^0), ISNUMBER(SEARCH({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, B3:B6))*1)

becomes

MMULT({1, 0, 0, 0, 0, 0, 0, 1, 0, 1;0, 0, 0, 0, 1, 0, 0, 0, 0, 1;0, 1, 0, 0, 0, 0, 1, 0, 1, 0;0, 0, 0, 0, 0, 1, 0, 1, 0, 0})

and returns

{1, 1, 0, 0, 1, 1, 1, 2, 1, 2}

#### Step 5 - Replace array values

The IF function returns one value if the logical expression evaluates to True and another value if False, this applies to an array of values as well.

IF(MMULT(TRANSPOSE(ROW(B3:B6)^0), ISNUMBER(SEARCH({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, B3:B6))*1), {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, "")

becomes

IF({1, 1, 0, 0, 1, 1, 1, 2, 1, 2}, {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, "")

0 (zero) equals False and any other value equals True.

IF({1, 1, 0, 0, 1, 1, 1, 2, 1, 2}, {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, "")

returns

{0,1,"","",4,5,6,7,8,9}

The image above shows the following array {0,1,"","",4,5,6,7,8,9} in row 7. It tells you that number two and three are not in cell range C3:C6.

#### Step 6 - Concatenate array values

The TEXTJOIN function is self-explanatory it joins values and is capable of ignoring blank values with any given delimiting character or string.

TEXTJOIN(*delimiter*,Â *ignore_empty*,Â *text1*,Â *[text2]*, ...)

TEXTJOIN("", TRUE, IF(MMULT(TRANSPOSE(ROW(B3:B6)^0), ISNUMBER(SEARCH({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, B3:B6))*1), {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, ""))

becomes

TEXTJOIN("", TRUE, {0,1,"","",4,5,6,7,8,9})

and returns "01456789".

#### Step 7 - Shorter formula

The LET function allows you to write shorter formulas by referring to names that contain a repeated expression. Our formula uses this array {0, 1, 2, 3, 4, 5, 6, 7, 8, 9} twice, we can use the LET function and name the array in order to simplify the formula.

LET(*name1*, *name_value1*, *calculation_or_name2*, [*name_value2*, *calculation_or_name3*...])

I named the array x and used x in two locations in the formula. This shortens the formula by 17 characters.

LET(x, {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, TEXTJOIN("", TRUE, IF(MMULT(TRANSPOSE(ROW(B3:B6)^0), ISNUMBER(SEARCH(x, B3:B6))*1), x, "")))

returns "01456789" in cell E2.

#### Will this formula work with blanks?

Yes, it works fine with blank cells in column B.

#### Can I mix text and numbers in column B?

Yes, the formula ignores letters and other characters.

#### Can I use values across columns?

No, the formula breaks if you try a multicolumn cell range like B3:C6.

### Extract category

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

Table of Contents Extract first word in cell value Extract the first word in cell - return warning if not […]

### Excel categories

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

You could always use the brute force method (which eliminates the need for any helper cells)...

=IF(COUNTIF(A1:A4,"*0*"), 0, "")&IF(COUNTIF(A1:A4, "*1*"), 1, "")&IF(COUNTIF(A1:A4, "*2*"), 2, "")&IF(COUNTIF(A1:A4, "*3*"), 3, "")&IF(COUNTIF(A1:A4, "*4*"), 4, "")&IF(COUNTIF(A1:A4, "*5*"), 5, "")&IF(COUNTIF(A1:A4,"*6*"), 6, "")&IF(COUNTIF(A1:A4, "*7*"), 7, "")&IF(COUNTIF(A1:A4, "*8*"), 8, "")&IF(COUNTIF(A1:A4, "*9*"), 9, "")

And for the VBA code fans out there, one could use this UDF (user defined function)...

Rick Rothstein (MVP - Excel),

Thank you for commenting.

Rick,

For me it's not working your formula (and UDF).

@Matt,

Just a guess, but your cells need to be formatted as Text before you enter the numbers into the cells or, alternately, enter your numbers into the cells preceded by an apostrophe (in order to make Excel see them as Text).

Can you bring unique within a single cell

mahmoud-lee,

See Rick Rothstein's formula.

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.

Gracias por compartir, es la mejor soluciÃ³n que he encontrado en internet, ademÃ¡s, muy bien explicada paso por paso. Â¡Un 10!