Do Not Want to count blank Cells

[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

 

[Bob Phillips]

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)

 

[Ron de Bruin]

Hi

Your cells are not empty
Maybe you have spaces in the cells

Try this on a copy of the workbook

Select the cells
Edit replace

Find what press the space bar one time
Replace with leave this empty

See if the counta function is working now

If not check out this site
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Guest]

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

 

[steve stadelhofer]

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

 

[Dave Peterson]

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.

 

[RagDyer]

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.

 

[Dave Peterson]

But isn't that slower if you have multiple columns to clean up?

 

[RagDyeR]

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.