FORMULA ERRORS CAUSED BY SPACEBAR

[Gator Girl]

For the longest time, I referred to this phenomena as the "Ghost".

Finally, I figured out what it is. If you hit the spacebar in a cell which
is part of a formula, the formula returns an error.

Is there anyway to tell Excel to ignore a blank space?

 

[Luke M]

Depends on how your formula is setup (example please?)
But yea, common annoyance is that if you have a space in a cell, or set a
cell formula to
=""
formulas will still not recognize it as blank. Perhaps include an IF
function that checks if cell value equals " "?

 

[Glenn]

For the longest time, I referred to this phenomena as the "Ghost".

Finally, I figured out what it is. If you hit the spacebar in a cell which
is part of a formula, the formula returns an error.

Is there anyway to tell Excel to ignore a blank space?

If the "Ghost" could be in A1, then replace A1 in your formula with TRIM(A1).

 

[JE McGimpsey]

Depends on the formula:

= A1 + B1

returns an error since math operators require numeric input. However,

=SUM(A1:B1)

ignores text.

You can also trap errors:

=IF(COUNT(A1,B1)=2, A1+ B1, "Need 2 numbers!")

 

[Shane Devenshire]

Hi,

1. Your going to need to show us the formula.
2. It is bad practice to clear cells with spacebar, you should use the Del
key.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Glenn]

If the "Ghost" could be in A1, then replace A1 in your formula with
TRIM(A1).

That may not work...maybe try N(A1).

 

[JE McGimpsey]

Absolutely correct.

However, if your workbook will be used by anyone else, it's best
practice to assume the user *will* use the spacebar to "clear" the cell.

I've at times had to resort to a Workbook-level event macro, e.g.:

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Excel.Range)
Dim rCell As Range
For Each rCell In Target
With rCell
If Len(Trim(.Value)) = 0 Then .ClearContents
End With
Next rCell
End Sub

to override years-long habits.

 

[Gator Girl]

Thanks for the input, Luke.
Guess I'll have to deal with the annoyance, since there are about 7 zillion
formulas I'd have to add the IF statement to.
Gator Girl

 

[Don Guillett]

=trim(a1)
=len(trim(a1))
so
=if(len(trim(a1))<1,"blank","the dreaded spacebar")

 

[Glenn]

You could use conditional formatting to highlight all "blank" cells that contain
spaces. Once highlighted, you could remove them manually and then the macro
recommended by JE McGimpsey would correct the behavior going forward.

 

[Gator Girl]

Hi. tried your suggestion, but it's not working for me. Here's a sample
formula:

=IF((G11+I11+K11+M11+O11+Q11+T11+V11+W11+Z11+AB11+AD11+AF11+AI11)>0,G11+I11+K11+M11+O11+Q11+T11+V11+W11+Z11+AB11+AD11+AF11+AI11),0)

.... and here's what I did (in Excel 2007):

I clicked on the worksheet tab, chose to "view code", and pasted your formula
between these two lines:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
(your formula)
End Sub

When I tested it by adding a spacebar stroke in a cell, the formula returned
the same old ######.

What am I doing wrong?

 

[Harlan Grove]

. . . Here's a sample formula:

=IF((G11+I11+K11+M11+O11+Q11+T11+V11+W11+Z11+AB11+AD11+AF11+AI11)>0,
G11+I11+K11+M11+O11+Q11+T11+V11+W11+Z11+AB11+AD11+AF11+AI11),0)

....

This isn't a valid formula, that is, Excel won't accept this as a
formula if you try to enter it because there are more right
parentheses than left parentheses. Provide an example formula Excel
actually allows you to enter.

If you mean something like

=IF(G11+I11+K11+M11+O11+Q11+T11+V11+W11+Z11+AB11+AD11+AF11+AI11>0,
G11+I11+K11+M11+O11+Q11+T11+V11+W11+Z11+AB11+AD11+AF11+AI11,0)

change it to either

=IF(N(G11)+N(I11)+N(K11)+N(M11)+N(O11)+N(Q11)+N(T11)+N(V11)+N(W11)
+N(Z11)+N(AB11)+N(AD11)+N(AF11)+N(AI11)>0,
N(G11)+N(I11)+N(K11)+N(M11)+N(O11)+N(Q11)+N(T11)+N(V11)+N(W11)
+N(Z11)+N(AB11)+N(AD11)+N(AF11)+N(AI11),0)

=IF(SUM(G11,I11,K11,M11,O11,Q11,T11,V11,W11,Z11,AB11,AD11,AF11,AI11)

0,

SUM(G11,I11,K11,M11,O11,Q11,T11,V11,W11,Z11,AB11,AD11,AF11,AI11),0)

or

=IF(COUNT
(G11,I11,K11,M11,O11,Q11,T11,V11,W11,Z11,AB11,AD11,AF11,AI11),
SUM(G11,I11,K11,M11,O11,Q11,T11,V11,W11,Z11,AB11,AD11,AF11,AI11),0)

 

[Gator Girl]

Thanks to all for their help. JE's solution seemed the least laborious, but
I couldn't get it to work, so I went with Harlan's. It worked perfectly.