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

=SEQUENCE(,5)

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.

=MID(A2,SEQUENCE(,LEN(A2)),1)

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 😕🤨

=ISNUMBER(MID(A2,SEQUENCE(,LEN(A2)),1))

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

=ISNUMBER(VALUE(MID(A2,SEQUENCE(LEN(A2)),1)))

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

=B2=TRUE

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

=FILTER(MID(A2,SEQUENCE(,LEN(A2)),1),ISNUMBER(VALUE(MID(A2,SEQUENCE(,LEN(A2)),1))))

Step 4: Group the numbers together

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

=TEXTJOIN("",,FILTER(VALUE(MID(A2,SEQUENCE(LEN(A2)),1)),ISNUMBER(VALUE(MID(A2,SEQUENCE(LEN(A2)),1)))))

Other solution

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

The formula he proposes is this one

=CONCAT(IFERROR(0+MID(A1,SEQUENCE(LEN(A1)),1),""))

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