How to Extract numbers from an Excel cell?

How to Extract numbers from an Excel cell?
Last Updated on 18/05/2023
Reading time: 2 minutes

How to extract only the digits from an Excel cell containing numbers and letters. To do that, we will use the new Excel functions: FILTER and SEQUENCE.

All the numbers return in a cell

What is the logic used to do the job?

To extract only the number from an Excel cell, we must split each character and keep the numbers.

  1. This work is made possible thanks to the new SEQUENCE function and the STXT function.
  2. Next, we will perform a test to find out if each of these characters is a number or not.
  3. Finally, when the test is true, we keep only the numbers.

Step 1: Extract each character from the cell

The MID function allows you to extract a sub-part of a character string. Associated with the SEQUENCE function, you create an array in which each cell will represent one of the characters in the cell.

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

Extract each character is a cell

Step 2: Test to know if the content is a number or not

Next, we need to perform a test on each of the cells to find out if the content is numeric or not.

So, of course, here, using the ISNUMBER function seems logical. However, at this stage, each of the cells contains the text.

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

We must, therefore, first convert each of the cells to a numerical value, if necessary. Thanks to the VALUE function, this conversion is automatic. The formula becomes.

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

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

Step 3: Keep only the numbers

To extract only the list of numbers, we will use the FILTER function. FILTER returns the rows when the test is true.

=FILTER(VALUE(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

Finally, the result of the FILTER expression will be embedded in a TEXTJOIN function

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

All the numbers return in a cell

1 Comment

  1. Rick Rothstein
    29/03/2023 @ 06:54

    Here is another formula that will retrieve the digits from a text string...

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

    Reply

Leave a Reply

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

How to Extract numbers from an Excel cell?

Reading time: 2 minutes
Last Updated on 18/05/2023

How to extract only the digits from an Excel cell containing numbers and letters. To do that, we will use the new Excel functions: FILTER and SEQUENCE.

All the numbers return in a cell

What is the logic used to do the job?

To extract only the number from an Excel cell, we must split each character and keep the numbers.

  1. This work is made possible thanks to the new SEQUENCE function and the STXT function.
  2. Next, we will perform a test to find out if each of these characters is a number or not.
  3. Finally, when the test is true, we keep only the numbers.

Step 1: Extract each character from the cell

The MID function allows you to extract a sub-part of a character string. Associated with the SEQUENCE function, you create an array in which each cell will represent one of the characters in the cell.

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

Extract each character is a cell

Step 2: Test to know if the content is a number or not

Next, we need to perform a test on each of the cells to find out if the content is numeric or not.

So, of course, here, using the ISNUMBER function seems logical. However, at this stage, each of the cells contains the text.

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

We must, therefore, first convert each of the cells to a numerical value, if necessary. Thanks to the VALUE function, this conversion is automatic. The formula becomes.

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

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

Step 3: Keep only the numbers

To extract only the list of numbers, we will use the FILTER function. FILTER returns the rows when the test is true.

=FILTER(VALUE(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

Finally, the result of the FILTER expression will be embedded in a TEXTJOIN function

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

All the numbers return in a cell

1 Comment

  1. Rick Rothstein
    29/03/2023 @ 06:54

    Here is another formula that will retrieve the digits from a text string...

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

    Reply

Leave a Reply

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