Split Comma-Separated Data and Sum a Population Column in WPS Spreadsheet
Question details
Split comma-separated city records into individual columns and total the population field accurately.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has imported city data stored in one comma-delimited column and needs to separate each field before calculating a population total.
- Observed behavior
- Each record is contained in a single cell or column, preventing the population values from being summed as a separate numeric field.
Check that commas are the only separators used between fields and that the destination columns to the right are empty. Identify which split column contains the population values before entering the SUM formula.
Use Text to Columns to split comma-delimited records
Use this method when every city record is in one column and commas separate the fields.
After splitting the records, the population field should appear in the fourth column if each row follows the same city-data layout. If the values are stored as text, convert them to numbers before calculating the total.
Click the column letter or select the cells that contain the comma-separated city records in WPS Spreadsheet.
Go to the Data tab and click Text to Columns.
Select Delimited, click Next, select the Comma check box, and clear any other delimiter check boxes that are selected.
Click Finish to place each comma-separated field into its own column.
Select an empty cell and enter =SUM(D:D) if the population values are in column D, then press Enter. If needed, select the population cells and use Home > Number Format to apply Number before summing.
Split and total comma-separated records with WPS Spreadsheet
WPS Spreadsheet lets you separate imported comma-delimited records with Text to Columns, format the resulting population field as numbers, and calculate totals with a standard SUM formula.
- 1. Open the data in WPS Spreadsheet: Open the worksheet containing the imported comma-separated records.
- 2. Select the source column: Click the column containing the unsplit city data.
- 3. Run Text to Columns: Choose Data > Text to Columns, select Delimited, and choose Comma as the separator.
- 4. Format the population values: Select the extracted population column and choose Home > Number Format > Number if the values are treated as text.
- 5. Calculate the total: Enter =SUM(D:D), replacing D with the column that contains the population values, and press Enter.
Frequently Asked Questions
Where is Text to Columns in WPS Spreadsheet?
Select the column to split, then go to the Data tab and click Text to Columns.
Why does my SUM formula return 0 after splitting the data?
The extracted population values may be stored as text. Select those cells, use Home > Number Format > Number, and then use a formula such as =SUM(D:D).
What delimiter should I select for city data separated by commas?
In the Text to Columns wizard, select Delimited, choose Comma, and clear other delimiter options unless the data uses them.
How do I total only the population cells instead of the entire column?
Enter a range-specific formula in an empty cell, such as =SUM(D2:D100), using the actual population column and row range.
