Sum Semicolon-Separated Numbers in One Excel Cell
Question details
Calculate the total of numbers stored as semicolon-separated text in a single cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A cell contains values such as 701;702;703;704, and the user wants their sum.
- Observed behavior
- The desired result is a numeric total of the values in the cell, preferably using one formula.
Identify the cell containing the separated values and check whether your Excel version supports TEXTSPLIT. The examples below assume the text is in A1.
Sum the values with a TEXTSPLIT formula
Use one formula in Excel versions that support TEXTSPLIT, including Microsoft 365 and Excel 2024.
Click an empty cell where you want the total to appear.
Type =SUM(NUMBERVALUE(TEXTSPLIT(A1,";"))) and press Enter. Replace A1 with the cell containing your text.
For 701;702;703;704 in A1, confirm that the formula returns 2810.

Split and sum the values with Text to Columns
Use this method when TEXTSPLIT is unavailable; it requires separate cells rather than a single formula.
Sum separated cell values in WPS Spreadsheets
In WPS Spreadsheets, split semicolon-separated text into columns and use SUM to calculate the total.
- 1. Open the spreadsheet: Open your workbook in WPS Spreadsheets and locate the cell containing the separated values.
- 2. Copy the text: Copy A1 to B1, or another cell with enough empty cells to its right.
- 3. Split the values: Select the copied cell and choose Data > Text to Columns. Select Delimited, choose Semicolon, and finish the wizard with the General format.
- 4. Calculate the total: In an empty cell, enter =SUM(B1:E1) for four values split into B1 through E1. Adjust the range if needed.

Frequently Asked Questions
What does the TEXTSPLIT formula do?
In =SUM(NUMBERVALUE(TEXTSPLIT(A1,";"))), TEXTSPLIT separates the text at each semicolon, NUMBERVALUE converts the pieces to numbers, and SUM adds them.
Can I use TEXTSPLIT in Excel 2021?
No. TEXTSPLIT is available in Microsoft 365 and Excel 2024, but not Excel 2021. In Excel 2021, use Data > Text to Columns to split the values, then sum the resulting cells.
Why does Excel reject the formula when I enter it?
Check that your version supports TEXTSPLIT and that A1 contains numbers separated by semicolons. Your regional settings may require semicolons instead of commas between formula arguments.
Will Text to Columns change my original cell?
It splits the selected cell and can overwrite adjacent cells. Copy the original text to an empty area first, then select the copy before choosing Data > Text to Columns.




