Extract only the numbers of a cell in Excel

Extract only the numbers of a cell in Excel
Last Updated on 18/11/2021
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

=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

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

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

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

6 Comments

  1. MohammadAli
    20/04/2023 @ 09:53

    Very good

    Reply

  2. Deep
    18/08/2022 @ 13:07

    I have one cell contains **9, how to get output just number in another cell? I mean 9 in another cell

    Reply

    • Frédéric LE GUEN
      19/08/2022 @ 10:56

      The symbol * is always a nightmare because it's also it means "all the characters". In this situation, I don't know if there is a solution.

      Reply

  3. Rick Rothstein
    13/11/2021 @ 07:35

    I believe this formula will also work...

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

    Reply

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

      BRILLIANT! Congrats for this formula Rick 😉

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Extract only the numbers of a cell in Excel

Reading time: 3 minutes
Last Updated on 18/11/2021

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

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

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

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

6 Comments

  1. MohammadAli
    20/04/2023 @ 09:53

    Very good

    Reply

  2. Deep
    18/08/2022 @ 13:07

    I have one cell contains **9, how to get output just number in another cell? I mean 9 in another cell

    Reply

    • Frédéric LE GUEN
      19/08/2022 @ 10:56

      The symbol * is always a nightmare because it's also it means "all the characters". In this situation, I don't know if there is a solution.

      Reply

  3. Rick Rothstein
    13/11/2021 @ 07:35

    I believe this formula will also work...

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

    Reply

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

      BRILLIANT! Congrats for this formula Rick 😉

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *