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