Dynamic Arrays Text functions

Extract only the numbers from a cell

Reading Time: 2 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

=SEQUENCE(,5)

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.

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

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

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

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.

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

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

=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))))

Keep only the numbers with FILTER

Step 4: Group the numbers together

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

All the numbers return in a cell

Related posts

What is the SPILL error?

Frédéric LE GUEN

Dynamic filter in Excel

Frédéric LE GUEN

How to return many rows with VLOOKUP

Frédéric LE GUEN

Leave a Comment