Tip Printed from ExcelTip.com

---

Summing Values in a Range Containing Redundant Spaces


Problem:

The numbers in column A contain redundant spaces.
If we try to calculate a total using the SUM function, 0 is returned.

Solution:

Use the SUM, VALUE, and SUBSTITUTE functions as shown in the following Array formula:
{=SUM(VALUE(SUBSTITUTE(A2:A4," ","")))}