Welcome to Excel Jet Consult blog post. In this tutorial, we learn how to extract numbers from alphanumeric values using FILTER and SEQUENCE Dynamic Array and other functions. We also see how to use use another set of functions to achieve the same functions. To watch the previous post, click on the link
For more explanation, kindly watch the video below
Sample Data
In the caption below, we have alphanumeric values in column A. We are interested in extracting only the numbers using formulas.
Example One
In the first example, we are going to use the following functions:
- LEN
- INDIRECT
- ROW
- IF
- ISNUMBER
- CONCAT
- SUM
- In cell B2, execute the formula: =SUM(CONCAT(IF(ISNUMBER(1+MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)),MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1),””)))
- Click Enter and copy down the formula using fill handle
From the caption below, we can see that the numbers are accurately extracted. See the video for in-depth explanation of the formula
Example One
In the second example, we are going to use the following functions:
- LEN
- SEQUENCE
- MID
- FILTER
- ISNUMBER
- CONCAT
- SUM
- In cell C2, execute the formula: =SUM(CONCAT(FILTER(MID(A2,SEQUENCE(LEN(A2)),1),ISNUMBER(1+MID(A2,SEQUENCE(LEN(A2)),1)))))
- Click Enter and copy down the formula using fill handle
From the caption below, numbers are extracted using Dynamic Array Functions and other functions. See the video for more in-depth explanation
Conclusion
We have seen how to extract numbers from alphanumeric values using different methods. Watch out for more videos as we extract text from alphanumeric values