Function Problems

How to Sum Numbers Embedded in Text in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to sum numerical values that are embedded within text strings across an Excel range, specifically isolating numbers located between specific characters like a dollar sign and a comma.

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Extracting and calculating the total of numbers hidden or embedded inside text strings across multiple cells without manually editing each cell.
Observed behavior: The user wants to achieve a total sum of isolated numeric values while ignoring the surrounding text, using advanced text extraction formulas.

Before you start

Ensure your data follows a consistent pattern, such as numbers always appearing between specific symbols (e.g., a dollar sign and a comma), and identify whether you are using a newer or older version of Excel.

Solution 1Recommended

Use TEXTBEFORE and TEXTAFTER (Newer Excel Versions)

Best for newer Excel versions (Excel 365, Excel 2021) that support dynamic arrays and advanced text manipulation functions.

This method uses the TEXTAFTER and TEXTBEFORE functions to isolate the number between a dollar sign ($) and a comma (,), then converts the text to numbers using the double unary operator (--), and finally sums them together.

Select output cell

Click on an empty cell where you want the final calculated total to appear.

Enter the formula

Type the formula: =SUM(--TEXTBEFORE(TEXTAFTER(D2:D100,"$"),",")) (adjust the range D2:D100 to match your actual data layout).

Calculate the result

Press Enter to execute the formula and view the extracted sum.

Handling #NAME? Errors: If you receive a #NAME? error, it indicates your current version of Excel does not support the TEXTBEFORE or TEXTAFTER functions. You should use the legacy array formula instead.

Solution 2

Use MID and SEARCH (Older Excel Versions)

Ideal for legacy versions of Excel that do not feature dynamic text extraction functions like TEXTBEFORE.

Efficient Data Management

Easily Extract and Sum Data Using WPS Spreadsheet

WPS Spreadsheet provides powerful text functions and robust array calculation capabilities that are fully compatible with Excel formulas. You can seamlessly process complex data, extract numbers from text strings, and run SUM formulas completely for free.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing your mixed text and number data.
2. Select the target cell: Click on the empty cell where you want to generate the total sum.
3. Apply the formula: Enter your preferred text-extraction sum formula and press Enter (or Ctrl+Shift+Enter for array formulas) to get instant results.

100% compatible with Microsoft Excel formulas, functions, and array processingSupports dynamic arrays and advanced text extraction toolsLightweight application that runs smoothly on almost any operating system

Frequently Asked Questions

Why does my formula return a #VALUE! error?

A #VALUE! error usually occurs if the target characters (like "$" or ",") are missing in some cells. Using the IFERROR function, as demonstrated in the older version method, helps handle these instances by returning 0 instead of breaking the entire calculation.

What does the double dash (--) do in the formula?

The double dash, known as the double unary operator, coerces the extracted text representation of a number into a true numeric value. Without it, the SUM function would ignore the extracted numbers because they are formatted as text.

Can I extract numbers without specific symbols surrounding them?

Yes, but it requires much more complex array formulas or VBA/Macro solutions to dynamically identify and extract numerical digits from varied text strings when there are no consistent delimiters like commas or dollar signs.

Excel & Data Problems