How to Calculate Survey Scores from Text Responses in Excel
Question details
The user needs a way to extract numerical values from text-based survey responses (e.g., '4 - Agree') to calculate an overall average satisfaction score across multiple questions.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing and averaging survey data where responses consist of a rating number followed by descriptive text.
- Observed behavior
- Because the survey responses are stored as text strings rather than pure numbers, standard mathematical functions like AVERAGE cannot process them directly.
Ensure your survey data consistently begins with a digit (e.g., '4 - Agree'). If the text comes first or if there are blank cells, the formula may return an error.
Use the AVERAGE and LEFT Functions to Extract and Calculate Scores
Extract the leading number from the text string using the LEFT function, convert it to a numerical value, and wrap it in the AVERAGE function.
The LEFT function is designed to pull a specified number of characters from the beginning of a text string. By placing a double negative (--) in front of it, the extracted text character is converted into a workable number that the AVERAGE function can process.
Modern spreadsheet software with dynamic array support allows this single formula to process a contiguous range of cells automatically without needing helper columns.
Click on the empty cell where you want the overall satisfaction average score to be displayed.
Type the formula =AVERAGE(--LEFT(A2:D2,1)) into the formula bar. Replace A2:D2 with the actual range containing your four survey responses.
Press the Enter key. If you are using an older version of Excel that does not support dynamic arrays, you may need to press Ctrl+Shift+Enter.
Navigate to the Home tab and use the Number Format dropdown to format the resulting value as a standard Number or Percentage as needed.

Analyze Survey Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides robust formula support, including advanced dynamic arrays and text-extraction functions, making complex survey data analysis fast and accurate.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing your survey results.
- 2. Select the target cell: Click on the cell at the end of the respondent's row where the average score should go.
- 3. Enter the formula: Type =AVERAGE(--LEFT(A2:D2,1)) and press Enter to instantly calculate the average.
- 4. Drag to fill: Click and drag the small square handle at the bottom-right corner of the cell down the column to apply the formula to all other respondents.

Frequently Asked Questions
What if my survey response doesn't start with a number?
If the number is at the end or embedded in the text (e.g., 'Agree - 4'), the LEFT function will not work. You will need to use the RIGHT function or the MID function. Alternatively, use the Flash Fill feature to extract the numbers into a new column first.
Why does the formula return a #VALUE! error?
This error usually occurs if one of the referenced cells is empty, or if the first character of the cell is not a number (such as a space or a letter). Ensure all referenced cells contain valid survey data starting with a digit.
Can I use this method to calculate the total sum instead of the average?
Yes. You can simply replace the AVERAGE function with the SUM function in the formula, making it =SUM(--LEFT(A2:D2,1)), to get the total cumulative score across the selected questions.




