# Extract only the numbers of a cell in Excel

Last Updated on 12/06/2024
Reading time: 3 minutes

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

## Understand the logic applied

To extract the numbers from the same cell with numbers and letters, we must follow the following logic

1. Extract each character, number, and letter into a single cell

This task is now possible with the new SEQUENCE function and the MID function

2. Create a test case for each character

In this situation, we will test is the character is numeric or not

3. Keep only the TRUE result

The FILTER function will help us to keep only the test case when the result is TRUE

4. Group all the numbers in a single cell

Finally, the TEXTJOIN function will gather each number found.

## Step 1: Extract each character from the cell

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

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 cell.

=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, we use the FILTER function to group only the numbers (when the test is TRUE).

• The first argument of the FILTER function is the first formula we have built on step 1
• The second argument is the test build on 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 solving 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 did 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 👏👏👏

## Frédéric LE GUEN

#### 6 Comments

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

Very good

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

• 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.

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

I believe this formula will also work...

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

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

BRILLIANT! Congrats for this formula Rick 😉

• Rick Rothstein
18/11/2021 @ 18:57

Thanks... I am glad you liked it.

# Extract only the numbers of a cell in Excel

Reading time: 3 minutes
Last Updated on 12/06/2024

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

## Understand the logic applied

To extract the numbers from the same cell with numbers and letters, we must follow the following logic

1. Extract each character, number, and letter into a single cell

This task is now possible with the new SEQUENCE function and the MID function

2. Create a test case for each character

In this situation, we will test is the character is numeric or not

3. Keep only the TRUE result

The FILTER function will help us to keep only the test case when the result is TRUE

4. Group all the numbers in a single cell

Finally, the TEXTJOIN function will gather each number found.

## Step 1: Extract each character from the cell

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

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 cell.

=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, we use the FILTER function to group only the numbers (when the test is TRUE).

• The first argument of the FILTER function is the first formula we have built on step 1
• The second argument is the test build on 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 solving 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 did 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 Articles

#### 6 Comments

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

Very good

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

• 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.

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

I believe this formula will also work...

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

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

BRILLIANT! Congrats for this formula Rick 😉

• Rick Rothstein
18/11/2021 @ 18:57

Thanks... I am glad you liked it.

{"mobile_concate":{"mobile-menu":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/mobile-menu.css"},"tf_base":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/css\/base.min.css","v":"7.8.2"},"themify_common":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/css\/themify-common.css","v":"7.8.2"},"builder-styles-css":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/themify-builder-style.css","v":"7.8.2"},"tb_tbp":{"s":"https:\/\/excel-tutorial.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/tbp-style.css","v":"3.6.3"},"tf_theme_site-logo":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-logo.css","v":"7.5.7"},"tf_theme_site-description":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-description.css","v":"7.5.7"},"tf_search_form":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/css\/modules\/search-form.css","v":"7.8.2"},"tb_divider":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/divider.css","v":"7.8.2"},"tb_post-content":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.css","v":"7.8.2"},"wp-block-library":{"s":"https:\/\/excel-tutorial.com\/wp-includes\/css\/dist\/block-library\/style.min.css","v":"6.6"},"tb_comments":{"s":"https:\/\/excel-tutorial.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/modules\/comments.css","v":"3.6.3"},"tb_image":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/image.css","v":"7.8.2"},"tb_image_top":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/image_styles\/top.css","v":"7.8.2"},"tb_text":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.css","v":"7.8.2"},"theme-style":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/style.css","v":"7.5.7"},"themify-media-queries":{"s":"https:\/\/excel-tutorial.com\/wp-content\/themes\/themify-infinite\/media-queries.css","v":"7.5.7","m":"(max-width:1200px)"}}