Do Not Want to count blank Cells

  • Thread starter Thread starter Steven Stadelhofer
  • Start date Start date
S

Steven Stadelhofer

I copy pasted an Access dynaset (e.g., results of a
crosstab query) into an Excel spreadsheet. I tried to
use the function =counta(a4:z4) to count the number of
entries (nonblank) in row 4. But the result is giving me
26 because it is counting blank cells. Is there a way to
count the entries without counting the blanks or a way to
easily reformat or change the blank cells so the formula
will not count the blanks. I do not want to have to
manually delete every blank cell in a table 26 columns by
2000 rows.
Thank you,
Steve
 
CountA does what you want. It seems that you must have something else in
those cells. Work out what it is, maybe spaces, and post back for a
solution.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The cells are blank - but when I try to use 'FIND' to
find a space it does not find a space. When I the delete
button to delete the cell it must remove whatever is in
the cell because the =counta calculates correctly. I was
trying to find a way - not to have to delete all of the
blank cells in a large spreadsheet.
steve
 
The cells are blank - but when I try to use 'FIND' to
find a space it does not find a space. When I the delete
button to delete the cell it must remove whatever is in
the cell because the =counta calculates correctly. I was
trying to find a way - not to have to delete all of the
blank cells in a large spreadsheet.
steve
 
I don't use access, but I've seen this problem with excel.

If you have a formula that evaluates to "" (looks blank) and then convert to
values, you don't get an empty cell.

(The =counta() will count it and
if you do Tools|Options|Transition tab|and check Transition navigation keys,
you'll see a single apostrophe in the formula bar))

I like to just do two edit|replace's.

Replace (leave blank)
with $$$$$$ (any unique set of characters not used)

then once more:
replace $$$$$$
with (leave blank)

And this will empty those cells.
 
What works for me *most* of the time that eliminates spaces, apostrophes and
<Alt><Enter> is:
<Data> <TextToColumns> <Next>
Check "Space", <Next> <Finish>.

For some reason, it doesn't work *all* of the time!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

I don't use access, but I've seen this problem with excel.

If you have a formula that evaluates to "" (looks blank) and then convert to
values, you don't get an empty cell.

(The =counta() will count it and
if you do Tools|Options|Transition tab|and check Transition navigation keys,
you'll see a single apostrophe in the formula bar))

I like to just do two edit|replace's.

Replace (leave blank)
with $$$$$$ (any unique set of characters not used)

then once more:
replace $$$$$$
with (leave blank)

And this will empty those cells.
 
You're right Dave, but the Sales & Marketing Group here deal with single and
double column imports, in the thousands of row size range.
Easier to teach them one simple procedure.
 
Back
Top