How to Extract and Sum Numbers Between Specific Text in Excel
Question details
The user needs to extract numbers located between specific text markers (like AANWEZIG and INI or INS) across multiple data ranges, and then calculate their total sum and weekly average.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Parsing complex strings containing mixed text and numbers to dynamically calculate numeric totals and averages from the dataset.
- Observed behavior
- Numbers are embedded within text strings and cannot be natively summed without first isolating them and converting them from text into numeric values.
Ensure you are using Microsoft 365 or a spreadsheet tool that supports dynamic array functions. For large datasets, defining a named range for your source data can make complex formula management much easier.
Isolate and Sum Using TEXTAFTER and TEXTBEFORE
Utilize modern Excel text manipulation formulas to slice the text, extract the number, convert the string to a calculable value, and sum it up.
This method uses TEXTAFTER and TEXTBEFORE to strip away the prefixes and suffixes of your target markers. By nesting these functions, you isolate only the number remaining between the markers.
Determine the array containing your mixed text and number data, for example, B3:F64.
Enter IF(SIGN(SEARCH("AANWEZIG*INI",B3:F64)),B3:F64,"") to identify cells that contain the target text structure using a wildcard.
Wrap the previous step with TEXTAFTER and TEXTBEFORE to remove the unwanted text. Use spaces (" ") as delimiters if your text markers are separated by spaces.
Wrap the extracted text in VALUE to convert it to a number, and IFERROR to output 0 for non-matching cells. Finally, encompass the entire formula in SUM: =SUM(VALUE(IFERROR(TEXTBEFORE(TEXTAFTER(IF(SIGN(SEARCH("AANWEZIG*INI",B3:F64)),B3:F64,"")," ")," "),0)))
Extract Numbers Using REGEXEXTRACT
For Microsoft 365 Insider or versions supporting regular expressions, REGEXEXTRACT offers a cleaner, more direct way to pull out numbers based on strict text patterns.
Use WPS Spreadsheet to Extract and Analyze Data
WPS Spreadsheet is a powerful, free tool providing comprehensive formula support for manipulating complex text data, extracting numbers, and performing rapid calculations without a steep learning curve.
- 1. Open your workbook: Launch WPS Office and open your .xlsx file containing the mixed text data in WPS Spreadsheet.
- 2. Select the destination cell: Click on the cell where you want the final sum or weekly average to be displayed.
- 3. Input the extraction formula: Type the combined formula using search and text extraction functions to isolate your target numbers across the data range.
- 4. Calculate the result: Press Enter to calculate the sum. You can copy the formula and adjust the text markers within the formula to calculate sums for other marker pairs like INS.
Frequently Asked Questions
Why does my extracted number return a #VALUE! error?
This happens if the extracted string contains unseen trailing spaces or non-printing characters. The VALUE function requires a clean numeric string. Wrapping your text extraction function in TRIM before applying VALUE often solves this issue.
Can I sum extracted numbers without using the VALUE function?
No, text extraction formulas like TEXTAFTER or MID output the result as text, even if it looks like a number. You must apply the VALUE function, multiply by 1, or use a double negative (--) to force the SUM function to recognize them as numbers.
What if I have older versions of Excel that don't support TEXTBEFORE and TEXTAFTER?
In older versions without dynamic text arrays, you will need to rely on a combination of MID, FIND (or SEARCH), and LEN to define the exact start and end positions of your numbers. This makes the formula longer but successfully achieves the same result.
