FORMULA ERRORS CAUSED BY SPACEBAR
 

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?

FORMULA ERRORS CAUSED BY SPACEBAR
 

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 " "?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Gator Girl" wrote:

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?

FORMULA ERRORS CAUSED BY SPACEBAR
 

Gator Girl wrote:
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).

FORMULA ERRORS CAUSED BY SPACEBAR
 

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!")




In article ,
Gator Girl wrote:

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?

FORMULA ERRORS CAUSED BY SPACEBAR
 

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

"Gator Girl" wrote in message
...
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?

FORMULA ERRORS CAUSED BY SPACEBAR
 

Glenn wrote:
Gator Girl wrote:
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).


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

FORMULA ERRORS CAUSED BY SPACEBAR
 

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.

In article ,
"Shane Devenshire" wrote:

2. It is bad practice to clear cells with spacebar, you should use the Del
key.

FORMULA ERRORS CAUSED BY SPACEBAR
 

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

"Luke M" wrote:

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 " "?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Gator Girl" wrote:

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?

FORMULA ERRORS CAUSED BY SPACEBAR
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gator Girl" wrote in message
...
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?

FORMULA ERRORS CAUSED BY SPACEBAR
 

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 wrote:
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

"Luke M" wrote:

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 " "?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Gator Girl" wrote:

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?

FORMULA ERRORS CAUSED BY SPACEBAR
 

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?





"JE McGimpsey" wrote:

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.

In article ,
"Shane Devenshire" wrote:

2. It is bad practice to clear cells with spacebar, you should use the Del
key.

FORMULA ERRORS CAUSED BY SPACEBAR
 

Gator Girl wrote...
. . . 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+A D11+AF11+AI110,
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(T1 1)+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,AB 11,AD11,AF11,AI11)
0,
SUM(G11,I11,K11,M11,O11,Q11,T11,V11,W11,Z11,AB11,A D11,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,A D11,AF11,AI11),0)

FORMULA ERRORS CAUSED BY SPACEBAR
 

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. :)

"Harlan Grove" wrote:

Gator Girl wrote...
. . . 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+A D11+AF11+AI110,
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(T1 1)+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,AB 11,AD11,AF11,AI11)
0,
SUM(G11,I11,K11,M11,O11,Q11,T11,V11,W11,Z11,AB11,A D11,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,A D11,AF11,AI11),0)