How to Average Semicolon-Separated Numbers in WPS Office Spreadsheets
Question details
The user needs to calculate the average of multiple numbers stored in Excel or WPS Office Spreadsheets cells separated by semicolons when a normal AVERAGE formula does not split them automatically.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a range such as A2:A100 where each cell contains several numbers separated by semicolons and wants a single average for all values.
- Observed behavior
- AVERAGE does not automatically split multiple numbers stored in one cell; the semicolon-separated values are treated as text instead of individual numbers.
Check whether your WPS Office Spreadsheets version supports TEXTSPLIT and TEXTJOIN before using the dynamic array formula. If you use Text to Columns instead, note that it will split the source cells into new columns and change the worksheet layout.
Use a TEXTSPLIT and TEXTJOIN Formula to Average Semicolon-Separated Values
Best when your WPS Office Spreadsheets or Excel version supports dynamic array functions; it calculates the average directly without changing the source data.
This formula first joins all source values into one semicolon-delimited string, splits them at each semicolon, and uses the double unary operator to convert text values to numbers before calculating the average.
Click the cell where you want the average result to appear, for example B2.
Type =AVERAGE(--TEXTSPLIT(TEXTJOIN(";",TRUE,A2:A100),";")) and press Enter.
Check that the formula returns the expected average. If you see #NAME?, verify that your version supports TEXTSPLIT and TEXTJOIN.
Split Semicolon-Separated Values with Text to Columns Before Averaging
Use this method when your WPS Office or Excel version does not support TEXTSPLIT, or when you prefer to keep a visible split dataset.
Average Semicolon-Separated Numbers with WPS Office Spreadsheets
WPS Office Spreadsheets lets you combine, split, and average semicolon-separated numbers using built-in functions or Text to Columns, so you can finish the task without buying Microsoft Excel.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the Excel file that contains the semicolon-separated numbers, and click the worksheet with the data.
- 2. Enter the TEXTSPLIT/TEXTJOIN formula: Click a blank cell and type =AVERAGE(--TEXTSPLIT(TEXTJOIN(";",TRUE,A2:A100),";")).
- 3. Use Text to Columns if needed: If your WPS Office version does not support TEXTSPLIT, select the source range and go to Data > Text to Columns to split the values by semicolon.
- 4. Calculate the average: Press Enter for the formula or apply =AVERAGE(B2:D100) to the split cells, then confirm the result.
Frequently Asked Questions
Why doesn't AVERAGE calculate semicolon-separated numbers in a single cell?
AVERAGE sees the cell content as one text string, not as separate numbers. You need to split the string with TEXTSPLIT or Text to Columns before averaging.
Does the TEXTSPLIT/TEXTJOIN formula work in every WPS Office version?
No. TEXTSPLIT requires a version with dynamic array support. If you see #NAME?, use Data > Text to Columns or Power Query to split the values first, then use AVERAGE.
Can I average the values without modifying the original data?
Yes. The formula =AVERAGE(--TEXTSPLIT(TEXTJOIN(";",TRUE,A2:A100),";")) calculates directly in the formula cell and leaves the source range unchanged.
What if my semicolon-separated cells contain text or empty values?
Empty values can be ignored when joining and splitting, but non-numeric text may cause #VALUE!. Clean or remove non-numeric entries, or split the data and convert the columns to numbers before applying AVERAGE.
