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