Strip Non-Numeric Characters Formula in Excel
Link to full course: https://commercecurve.com/ultimate-ex...
Take this course if you're looking to accelerate your career with a complete course that integrates finance and accounting topics with end-to-end Excel training through the application of concrete examples of real world companies.
There is a string of formula that can be written to strip out only the non-numerical values from a cell and only return the numerical values. This is useful in situations where you need to calculate the sum of data contained in a cell which has non-numeric values or if you need only numeric portions of a text string. e.g. for serial numbers or other purposes.
=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,""))
In the above formula, every item except A1 will be same for every situation. Instead of typing A1 you would select the cell containing the value you would like to strip.
This formula uses the MID formula which works from the inside out to remove non-numeric characters.
The ROW and INDIRECT functions act to reference an array of the numbers between 1 and 100.