 # Extract Numbers From Alphanumeric Values using Multiple Functions

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn one of the most unique way of extracting numbers from alphanumeric values using multiple functions. Let’s get started

For more explanation, kindly watch the video below

Sample Data

In the caption below, we have alphanumeric values in column A formatted in an Excel Table. We are interested in extracting only the numbers using formulas. Thereafter, we are going copy the data in column D List of Functions

To achieve the extraction of numbers, we will use the following functions:

• LEN
• INDIRECT
• ROW
• MID
• ISNUMBER
• IF
• LARGE
• MID
• IFERROR
• POWER
• SUMPRODUCT

CALCULATIONS

• In cell B2, execute the formula:

=SUMPRODUCT(IFERROR(MID([@Data],LARGE(IF(ISNUMBER(1+MID([@Data],ROW(INDIRECT(“1:”&LEN([@Data]))),1)),ROW(INDIRECT(“1:”&LEN([@Data]))),0),ROW(INDIRECT(“1:”&LEN([@Data])))),1),0)*POWER(10,ROW(INDIRECT(“1:”&LEN([@Data])))-1))

Do Refer to the video for step by step explanation of the giant formula

• Click Enter

From the caption below, we can see that we have successfully extracted all the numbers which make sense Copy the data in column D and Paste in cell A9. The data is automatically incorporated into the Table. See the caption below Conclusion

Although, the formula is long, ultimately, we extracted all the numbers successfully. In Part Two, I will show us how to perform the same operation using Dynamic Array and other functions. In the mean time, take time to go through the video and subscribe to my Channel if you haven’t done so

Posted in Blog.