BLANKS AND SPACES

[Guest]

WHEN I USE THE FORMULA (COUNT A) IT COUNTS ALL NON BLANK CELLS , BUT IF YOU HIT THE SPACE BAR IT THEN COUNTS THE CELL AS A NON BLANK AND COUNTS IT --- REMEDY
PROBLEM --- EXCELL -- LOOKS AT ALL ,BLANKS,ZERO'S,SPACES ,TEXT AS 0'S, ALL AS ZERO

 

[Paul B]

Bill, if the cell has a space in it, it is not blank, if you are trying to
count numbers you can use =count(), does this help or do you need something
else?

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

WHEN I USE THE FORMULA (COUNT A) IT COUNTS ALL NON BLANK CELLS , BUT IF

YOU HIT THE SPACE BAR IT THEN COUNTS THE CELL AS A NON BLANK AND COUNTS
IT --- REMEDY?

PROBLEM --- EXCELL -- LOOKS AT ALL ,BLANKS,ZERO'S,SPACES ,TEXT AS 0'S,

ALL AS ZERO

 

[RWN]

A space is a character, not a "blank" in your context.
XL uses the term "empty" cell (I prefer the term "Null") meaning
absolutely nothing is in the cell (what you get when you "Delete" a
value in a cell).

--
Regards;
Rob
------------------------------------------------------------------------

WHEN I USE THE FORMULA (COUNT A) IT COUNTS ALL NON BLANK CELLS , BUT

IF YOU HIT THE SPACE BAR IT THEN COUNTS THE CELL AS A NON BLANK AND
COUNTS IT --- REMEDY?

PROBLEM --- EXCELL -- LOOKS AT ALL ,BLANKS,ZERO'S,SPACES ,TEXT AS

0'S, ALL AS ZERO

 

[Peo Sjoblom]

For future posting please refrain from using all caps, it's considered
shouting
and all caps are harder to read..
As other have told you, a space does not make a cell blank as in null but if
you have that in mind you can use

=SUMPRODUCT(--(TRIM(A1:A200)<>""))

to count non blanks

--

Regards,

Peo Sjoblom

WHEN I USE THE FORMULA (COUNT A) IT COUNTS ALL NON BLANK CELLS , BUT IF

YOU HIT THE SPACE BAR IT THEN COUNTS THE CELL AS A NON BLANK AND COUNTS
IT --- REMEDY?

PROBLEM --- EXCELL -- LOOKS AT ALL ,BLANKS,ZERO'S,SPACES ,TEXT AS 0'S,

ALL AS ZERO

 

[Dave Peterson]

Excel does have a worksheet formula that's called =countblank().

But that, too, won't include cells with spaces. (I think that this is a bad
habit to pick up. It's much better to hit the delete key (edit|clear|contents)
when you want to empty the cell.)

 

[Dave Peterson]

I'm not sure if this is important, but this is limited by the usedrange.

 

[Don Guillett]

It is considered rude to type in all CAPS. Called "shouting"
The patient said to the doctor, "Doctor, it hurts when I do this". The
doctor said, " Then don't do that"

--
Don Guillett
SalesAid Software
(e-mail address removed)

WHEN I USE THE FORMULA (COUNT A) IT COUNTS ALL NON BLANK CELLS , BUT IF

YOU HIT THE SPACE BAR IT THEN COUNTS THE CELL AS A NON BLANK AND COUNTS
IT --- REMEDY?

PROBLEM --- EXCELL -- LOOKS AT ALL ,BLANKS,ZERO'S,SPACES ,TEXT AS 0'S,

ALL AS ZERO

 

[Harlan Grove]

A space is a character, not a "blank" in your context.
XL uses the term "empty" cell (I prefer the term "Null") meaning
absolutely nothing is in the cell (what you get when you "Delete" a
value in a cell).

....

However, 'Null' already has a precise meaning in Excel that means something
else, e.g., range objects containing no cells such as =A:A B:B. 'Empty' is
the most applicable VBA term - the value of an unset variant type variable,
but 'blank' is the worksheet term. Best not to confuse less experienced
users with your own nonstandard terminology.

 

[Harlan Grove]

Excel does have a worksheet formula that's called =countblank().

But that, too, won't include cells with spaces.

?

In Excel 97 SR-2 (I), given =COUNTA(A1:A6) equal to 1 with A5 containing the
formula ="", =COUNTBLANK(A1:A6) returns 6. What did you mean by 'include'?

 

[Dave Peterson]

I really meant space characters:

range("a1").value = " "

They look blank to the naked eye, but won't be included in the total that
=countblank() returns.

 

[RWN]

Granted, but as the question related to a Worksheet function, and having
had to deal with this question at work over the years, I've found that
the "less experienced" users understood the concept better if I used the
term "null".
A lot of people view the term "Blank" and "Space" as the same thing and
get confused by thinking that if they can't see it then it must be
"Empty".