Excel #VALUE! error in simple formula

I've come across the dreaded "space in a cell" scenario quite a few times in client Excel spreadsheets.  Some seem to think that [space]+[enter] is the same as [delete]. The trouble is that if you try to perform any arithmetic using the cell reference alone you'll get a #VALUE! error.
With the formula =A2+B2-C2 you'll get the error if you put non-numeric text in any of the three cells.

There are several different strategies I employ in these types of scenario. 

One observation is that the Sum() function will treat a space (or any text for that matter) as zero. So, instead of A2+B2-C2 you can write the formula as Sum(A2)+Sum(B2)-Sum(C2) or Sum(A2,B2)-Sum(C2). Note that you cannot write Sum(A2,B2,-C2) or a space character in cell C2 will provoke a #VALUE! error - it tries to calculate the negative of a space character before the Sum function has had a chance to turn it into a zero for arithmetic purposes. An element of error proofing is thus applied to your spreadsheets if you always wrap individual cell references with Sum().

However, you'll often come across this where it may be very time consuming to change an existing spreadsheet to employ this strategy.  In these cases I find that the Edit | Goto (F5 key) followed by Special... and then selecting the Constants radio button in combination with only the Text tick box identifies all cells with text in them.  The ones that look empty are the ones with spaces in them.  Delete the spaces and things should start working again.

Last, but not least, if there ought not to be any valid space characters in the highlighted cells, I would use search and replace.  A single space character goes in the "Find what:" field and the "Replace with:" field is left empty. 

I hope this helps.

Replacement function for GETPIVOTDATA

Why is GETPIVOTDATA so cumbersome to work with!  I'm always having to do stuff like:
=if(iserror(GETPIVOTDATA([parameter set])),0,GETPIVOTDATA([parameter set]))

It gets to be a real pain as I have to edit both [parameter set]s to have it work properly and there's always the possibility of making a mistake that is not immediately apparent.

I finally decided I'd create my own function that returned 0 instead of erroring:

Function GETMYPIVOTDATA(data_field, pivot_table, _
    Optional field1, Optional item1, _
    Optional field2, Optional item2, _
    Optional field3, Optional item3, _
    Optional field4, Optional item4, _
    Optional field5, Optional item5, _
    Optional field6, Optional item6, _
    Optional field7, Optional item7, _
    Optional field8, Optional item8, _
    Optional field9, Optional item9, _
    Optional field10, Optional item10, _
    Optional field11, Optional item11, _
    Optional field12, Optional item12, _
    Optional field13, Optional item13, _
    Optional field14, Optional item14)
Dim retval
Err.Clear
On Error Resume Next
retval = pivot_table.pivottable.GetPivotData(data_field, _
    field1, item1, field2, item2, field3, item3, field4, item4, _
    field5, item5, field6, item6, field7, item7, field8, item8, _
    field9, item9, field10, item10, field11, item11, field12, item12, _
    field13, item13, field14, item14 _
    )
On Error GoTo 0
GETMYPIVOTDATA = IIf(IsError(retval), 0, retval)
End Function

Now I just use =GETMYPIVOTDATA([parameter set])