Function Problems

Sum Semicolon-Separated Numbers in One Excel Cell

Steve KSteve K Nov 18, 2026 869 views

Question details

Calculate the total of numbers stored as semicolon-separated text in a single cell.

How to Sum Semicolon-Separated Numbers in One Excel 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.
Before you start

Identify the cell containing the separated values and check whether your Excel version supports TEXTSPLIT. The examples below assume the text is in A1.

Solution 1Recommended

Sum the values with a TEXTSPLIT formula

Use one formula in Excel versions that support TEXTSPLIT, including Microsoft 365 and Excel 2024.

1
Select an empty cell

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

2
Enter the formula

Type =SUM(NUMBERVALUE(TEXTSPLIT(A1,";"))) and press Enter. Replace A1 with the cell containing your text.

3
Check the result

For 701;702;703;704 in A1, confirm that the formula returns 2810.

Sum the values with a TEXTSPLIT formula
Function availability: TEXTSPLIT is not available in Excel 2021. If Excel reports an unrecognized function, use Text to Columns instead. Depending on your regional settings, you may also need semicolons rather than commas between formula arguments.
Work with spreadsheet data

Sum separated cell values in WPS Spreadsheets

In WPS Spreadsheets, split semicolon-separated text into columns and use SUM to calculate the total.

  1. 1. Open the spreadsheet: Open your workbook in WPS Spreadsheets and locate the cell containing the separated values.
  2. 2. Copy the text: Copy A1 to B1, or another cell with enough empty cells to its right.
  3. 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. 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.
Split semicolon-separated values using Text to ColumnsSum the resulting cells with a familiar spreadsheet formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.