Function Problems

How to Sum Numbers Included in Text Responses in Excel

Adam Davis Sep 25, 2026 869 views

Question details

The user needs to calculate the sum of numerical values that are embedded within text strings in spreadsheet cells.

How to Sum Numbers Included in Text Responses in Excel

Product: Excel
Device & OS: not provided
Scenario: Adding up values from survey responses or dropdowns that contain both text and numbers (e.g., "Disagree a lot = 4").
Observed behavior: The data is formatted as mixed text and numbers, meaning standard sum functions will not work without first extracting the numeric portion.

Before you start

Ensure that your text entries follow a consistent pattern, such as having the number always appear after a specific delimiter (like an equals sign), so the extraction formula can target it accurately.

Solution 1Recommended

Use SUM and TEXTAFTER Functions

Extract the number located after a specific delimiter using TEXTAFTER, convert it to a value, and sum it up.

This method is highly efficient for modern spreadsheet software supporting dynamic arrays and the TEXTAFTER function. By defining the equals sign (=) as the delimiter, the formula isolates the number.

Select the destination cell

Click on the cell where you want the final total to be displayed.

Enter the formula

Type the formula: =SUM(--TEXTAFTER(B2:C2, "=")) into the formula bar.

Calculate the result

Press Enter. The TEXTAFTER function extracts the text after the '=', the double minus (--) converts the extracted text into numeric values, and SUM adds them together.

Version Compatibility: The TEXTAFTER function is available in newer versions of Excel (like Microsoft 365). If you are using an older version, you will need to use a combination of RIGHT, LEN, and FIND functions instead.

Solution 2

Use SUMPRODUCT, RIGHT, and FIND Functions (Legacy Version)

For older spreadsheet versions that do not support TEXTAFTER, use traditional text functions to separate the numbers before summing.

Solve Spreadsheet Problems Easily

Use WPS Spreadsheet to Handle Complex Formulas and Text Extraction

WPS Spreadsheet provides powerful data processing capabilities, including advanced text and math functions. It easily handles array formulas, letting you extract numbers from text strings and calculate accurate sums without hassle.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the mixed text data.
2. Select your total cell: Click on the cell where the calculated sum needs to be displayed.
3. Enter the extraction formula: Input your preferred text extraction formula (such as combining SUMPRODUCT and RIGHT) into the formula bar.
4. Get instant results: Press Enter to instantly evaluate the array and get your total, utilizing the fast calculation engine of WPS.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formatsComprehensive suite of text and math functions for advanced data analysisLightweight, fast, and completely free to use across Windows, Mac, and mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

What does the double minus (--) do in Excel formulas?

The double minus, known as a double unary operator, is used to coerce text strings that contain numbers into actual numeric values. This step is necessary because text extraction functions return text formats, which math functions like SUM ignore otherwise.

How can I extract numbers if they are at the beginning of the text?

If your numeric values appear before the text (e.g., '4 = Disagree a lot'), you can use the TEXTBEFORE function instead, or combine the LEFT and FIND functions to extract characters up to the delimiter.

Why am I getting a #VALUE! error when summing extracted text?

A #VALUE! error usually occurs if the extracted text contains non-numeric characters, like hidden trailing spaces, which disrupts the conversion to a number. Wrapping your text extraction function inside a TRIM function often resolves this issue.

Excel & Data Problems