How to Sum Excel Cells That Contain Numbers and Text in WPS Office
Question details
The user needs to sum cells that mix numbers and text, where a direct SUM formula may ignore the numeric portion or return an error.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- Data cells contain entries such as "12 apples" and the user wants to total the numeric values embedded in the text.
- Observed behavior
- Direct SUM formulas may skip these cells or produce errors because WPS Spreadsheet and Excel treat them as text.
Check your WPS Office version and identify the delimiter, such as a space, that separates the numbers from the text in the affected cells. If the data has inconsistent delimiters, plan to split the values into helper columns before summing.
Use an array formula with TEXTBEFORE and IFERROR to extract and sum numbers
Best for a single-cell total when numeric values appear before a consistent delimiter such as a space.
The formula extracts the part of each cell before the delimiter, converts it to a number, ignores errors, and returns the total without changing the original data layout.
Click the cell where you want the sum to appear, such as B1.
Type =SUM(IFERROR(--TEXTBEFORE(A1:A10," "),0)) and adjust A1:A10 and the delimiter in quotes to match your data range.
Press Enter to calculate the result. In current WPS Office versions the formula calculates as an array; in older versions, confirm with Ctrl+Shift+Enter if prompted.
Split mixed cells into separate columns with Text to Columns and then sum
Use this when you prefer a visible helper column or when formula support is limited in your WPS Office version.
Restructure data so numbers and text stay in separate columns
Best long-term fix for reliable calculation and cleaner data management.
Sum Numbers Inside Text Cells in WPS Office Spreadsheet
WPS Office Spreadsheet can extract and sum numeric values from mixed text cells with formulas or Text to Columns, so you do not need to manually retype data. It gives you a compatible, familiar spreadsheet environment for Excel files.
- 1. Open WPS Office Spreadsheet: Launch WPS Office and open the Excel workbook that contains the mixed number and text cells.
- 2. Select a blank result cell: Click a blank cell where you want the total to appear.
- 3. Enter the extraction formula: Type =SUM(IFERROR(--TEXTBEFORE(A1:A10," "),0)) and adjust the range and delimiter to match your data.
- 4. Press Enter: Press Enter or Ctrl+Shift+Enter if your version requires it to calculate the total.
- 5. Check the total: Confirm the result matches the expected sum of the numeric portions.
Frequently Asked Questions
Which formula sums cells that contain both numbers and text?
In WPS Office Spreadsheet you can use =SUM(IFERROR(--TEXTBEFORE(A1:A10," "),0)). Replace A1:A10 with your range and the delimiter with the character that separates the number from the text.
Does WPS Office support the TEXTBEFORE function?
Current WPS Office versions support many modern Excel functions, including TEXTBEFORE. If your version does not list it, use the Data tab > Text to Columns to split the data, or use a helper column with LEFT and FIND to extract the number before summing.
Why does a normal SUM ignore cells with numbers and text?
Cells that contain letters alongside numbers are stored as text. A regular SUM function only adds numeric cells, so those text cells are ignored or may contribute 0 until the number is extracted or the cell is converted to a number.
How can I split numbers and text into separate columns in WPS Office?
Select the cells, go to the Data tab > Text to Columns, choose Delimited, select the delimiter such as a space, and finish the wizard. Then use a simple =SUM on the resulting numeric column.
Is it better to store numbers and text in separate columns?
Yes. Keeping numeric values in one column and labels in another makes formulas like SUM, AVERAGE, and charts work reliably and avoids complex extraction formulas or errors.
