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
- Extract each character, number, and letter into a single cell
This task is now possible with the new SEQUENCE function and the MID function
- Create a test case for each character
In this situation, we will test is the character is numeric or not
- Keep only the TRUE result
The FILTER function will help us to keep only the test case when the result is TRUE
- 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 👏👏👏
MohammadAli
Deep
20/04/2023 @ 09:53
Very good
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
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.
13/11/2021 @ 07:35
I believe this formula will also work...
=CONCAT(IFERROR(0+MID(A1,SEQUENCE(LEN(A1)),1),""))
18/11/2021 @ 17:22
BRILLIANT! Congrats for this formula Rick 😉
18/11/2021 @ 18:57
Thanks... I am glad you liked it.