Extract Numbers From Alphanumeric Values using Dynamic Array Functions

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

Posted in Blog.