Introduction
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to extract numbers from alphanumeric values across multiple columns and then summed. We will combination of several high-level Excel functions spanning over 600 length of characters! Let’s get started
Sample Data
In the caption below, we have our sample data in columns A and B. From the dataset, we the following numbers in cell A2: 1255003 and 790990 in cell B2 which we want to first extract and summed
List of Functions
For this exercise, we will use the following functions
- LEN Function
- INDIRECT Function
- ROW Function
- MID Function
- ISNUMBER Function
- IF Function
- LARGE Function
- POWER Function
- SUMPRODUCT Function
Formula
- In cell C2, execute the formula:
=SUMPRODUCT(SUMPRODUCT(IFERROR(MID(A2,LARGE(IF(ISNUMBER(1+MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)),ROW(INDIRECT(“1:”&LEN(A2))),0),ROW(INDIRECT(“1:”&LEN(A2)))),1),0)*POWER(10,ROW(INDIRECT(“1:”&LEN(A2)))-1)))+(SUMPRODUCT(IFERROR(MID(B2,LARGE(IF(ISNUMBER(1+MID(B2,ROW(INDIRECT(“1:”&LEN(B2))),1)),ROW(INDIRECT(“1:”&LEN(B2))),0),ROW(INDIRECT(“1:”&LEN(B2)))),1),0)*POWER(10,ROW(INDIRECT(“1:”&LEN(B2)))-1)))
- Click Enter and copy down the formula
Kindly refer to the video for the definitive step by step explanation of each component of the functions
From the caption below, the summed of the extracted numeric values across the two columns is $7,045,993
Conclusion
Although, this is quite a long and complex formula of about 600 characters long, it is worth knowing that it is possible to extract numbers from alphanumeric values.
Kindly refer to the video for more explanation