Dynamic Arrays Text functions

Extract only the numbers of a cell in Excel

Reading Time: 3 minutes

This article will explain you how to extract the numbers from a cell containing numbers and letters.

To do that, we will use the new functions of Excel 365 ; FILTER and SEQUENCE 

Understand the logic applied

To be able to extract the numbers and letters from the same cell, we have no choice ; extract each single character into the cell.

  1. This task is now possible with the new SEQUENCE function and the MID function .
  2. Next, we will perform a test to find out if each one of these characters is a number or not.
  3. Finally, when the test is true. So we just keep the numbers.

Step 1: Extract each character from the cell

The SEQUENCE function generates a series of number between 2 values. For instance to create a series of number between 1 and 5 in column, you will write


Principe of the SEQUENCE function

The trick here, is to use the SEQUENCE function to split each character of the cells with this formula with the MID function. Also, the LEN function will return the exact number of characters in each cells.


Extract each character is a cell

Step 2: Test if each character is a number or not

Then we need to perform a test on each one of these characters to find out if it's a number or not.

So of course here, using the ISNUMBERfunction seems logical. However, at this stage, each of the cells contains text 😕🤨


At this step all the characters are TEXT

We can easily correct this with the function VALUE. VALUE will automatically convert a character to a number if needed.


With VALUE we are able to test when a character is a number

To highlight the cells when the result is true, I have used the following conditional formatting rule


Step 3: Isolate the numbers

Now, to group only the numbers (when the test is TRUE), we use the FILTER function.

The first argument of the FILTER function is the first formula we have build on step 1

The second argument is the test build on the step 2


Keep only the numbers with FILTER

Step 4: Group the numbers together

This is the last step 😀 We just have to group the previous result with the TEXTJOIN function.


All the numbers return in a cell

Other solution

Rick Rothsein, another Microsoft Excel MVP, has another approach to solve this problem.

The formula he proposes is this one


He reused the technique to combine MID, SEQUENCE and LEN but instead of using the FILTER function, he just do a calculation with 0. So, when it's letter + 0, it's an error. When it's 0 + number, the formula returns a number. This is why he used IFERROR to keep only the numbers (clever 😉)

Thanks a lot Rick for sharing your formula 👏👏👏

Related posts

SORT your data dynamically

Frédéric LE GUEN

What is the SPILL error?

Frédéric LE GUEN

Extract items that appear only one time

Frédéric LE GUEN


Rick Rothstein 13/11/2021 at 07:35

I believe this formula will also work...


Frédéric LE GUEN 18/11/2021 at 17:22

BRILLIANT! Congrats for this formula Rick 😉

Rick Rothstein 18/11/2021 at 18:57

Thanks... I am glad you liked it.


Leave a Comment