Need to find average value for a column in sheet summary; contains number and text.

tina_ajuria24

We have (5) survey questions with a number and description:

1 - strongly disagree; 2 - disagree, etc.

I need to find the average in the sheet summary; what is the correct formula to extract the text portion and only retain the average of the numerical value?

Thank you!

Paul Newcome

You will need a helper column to extract the number from the string and convert it into a numerical value.

=VALUE(LEFT([Column Name]@row))

Then you would run a basic AVG function on this helper column.

Paul Newcome

You will need a helper column to extract the number from the string and convert it into a numerical value.

=VALUE(LEFT([Column Name]@row))

Then you would run a basic AVG function on this helper column.

tina_ajuria24

That works!! Thank you SO much for your assistance!

Paul Newcome

Happy to help. 👍️