Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JackieD
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C. Any ideas?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

Countblank(range)
actually does count blank as well as empty cells

"JackieD" wrote:

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C. Any ideas?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JackieD
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

I don't want Excel to count my "blanks". I want it to ignore them. I only
want to count cells that have dates in them.

"bj" wrote:

Countblank(range)
actually does count blank as well as empty cells

"JackieD" wrote:

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C. Any ideas?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

When I do importing of data, I usually find that I must "clean" the data.
That is, get rid of undesirable and unseen characters. Cell-View will tell
me what is in the cells, and Edit Replace will convert them to
"nothing".....or "ASAP Utilities" has a feature that will help.

hth
Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access and

Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them,

and
my count changes. So, can I get Excel to put a value into my "blank"

cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for

A,
3 for B and 1 for C. Any ideas?



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

Try using COUNT instead of COUNTA.....

Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
I don't want Excel to count my "blanks". I want it to ignore them. I only
want to count cells that have dates in them.

"bj" wrote:

Countblank(range)
actually does count blank as well as empty cells

"JackieD" wrote:

Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access and

Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've

tested
this theory by highlighting a couple of "blank" cells and deleting

them, and
my count changes. So, can I get Excel to put a value into my "blank"

cells,
so then I could filter it out, or create a formula that would only

count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2

for A,
3 for B and 1 for C. Any ideas?





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SteveG
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank


The only problem with COUNTBLANK is that if your imported data is
creating spaces in the cells where it appears to be "blank", COUNTBLANK
will ignore those cells. When you select one of the cells, is there a
single apostrophe in the formula bar? If you type in a single
apostrophe on a blank sheet you'll see that it does not appear in the
cell but it does in the formula bar. If not than my guess is it is
probably spaces in the "blank" cells that are your problem. Try using
just the COUNT function, not COUNTA to get the number of dates, not
blanks. COUNT counts numbers which it recognizes dates as. It will
not count text or spaces etc. I just tried this using both the space
in a cell of my range and an apostrophe and came up with the right
number of cells that contained dates rather than blanks.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=515051

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

See if one of these works for you:

If you want to clean the "blank" cells, try this:
This works for me (and I don't know why)

Select the data range
EditFind
Don't enter anything in the Find What box.
Click the [Find All] button
(That should display a list of all of the cells you are interested in)
Hold down the [Ctrl] key and press the letter A
(That should select all of the matching cells)
Click the [Close] button to close the window

Press the [Delete] key to clear those cells.

OR

Otherwise, since Excel stores dates as numbers, just use the COUNT function
to count the dates.

Does either of those help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JackieD" wrote:

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C. Any ideas?

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JackieD
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

What commands do I use to tell Excel to only count the cells that have a
number in date format?

"CLR" wrote:

Try using COUNT instead of COUNTA.....

Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
I don't want Excel to count my "blanks". I want it to ignore them. I only
want to count cells that have dates in them.

"bj" wrote:

Countblank(range)
actually does count blank as well as empty cells

"JackieD" wrote:

Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access and

Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've

tested
this theory by highlighting a couple of "blank" cells and deleting

them, and
my count changes. So, can I get Excel to put a value into my "blank"

cells,
so then I could filter it out, or create a formula that would only

count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2

for A,
3 for B and 1 for C. Any ideas?




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

COUNT

Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
What commands do I use to tell Excel to only count the cells that have a
number in date format?

"CLR" wrote:

Try using COUNT instead of COUNTA.....

Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
I don't want Excel to count my "blanks". I want it to ignore them. I

only
want to count cells that have dates in them.

"bj" wrote:

Countblank(range)
actually does count blank as well as empty cells

"JackieD" wrote:

Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access

and
Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've

tested
this theory by highlighting a couple of "blank" cells and deleting

them, and
my count changes. So, can I get Excel to put a value into my

"blank"
cells,
so then I could filter it out, or create a formula that would only

count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of

2
for A,
3 for B and 1 for C. Any ideas?






  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JackieD
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

That did it!!! Thanks!

"Ron Coderre" wrote:

See if one of these works for you:

If you want to clean the "blank" cells, try this:
This works for me (and I don't know why)

Select the data range
EditFind
Don't enter anything in the Find What box.
Click the [Find All] button
(That should display a list of all of the cells you are interested in)
Hold down the [Ctrl] key and press the letter A
(That should select all of the matching cells)
Click the [Close] button to close the window

Press the [Delete] key to clear those cells.

OR

Otherwise, since Excel stores dates as numbers, just use the COUNT function
to count the dates.

Does either of those help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JackieD" wrote:

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C. Any ideas?



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

what do you mean by "Cell-View"?

"CLR" wrote:

When I do importing of data, I usually find that I must "clean" the data.
That is, get rid of undesirable and unseen characters. Cell-View will tell
me what is in the cells, and Edit Replace will convert them to
"nothing".....or "ASAP Utilities" has a feature that will help.

hth
Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access and

Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them,

and
my count changes. So, can I get Excel to put a value into my "blank"

cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for

A,
3 for B and 1 for C. Any ideas?




  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Sloth wrote:

what do you mean by "Cell-View"?

"CLR" wrote:

When I do importing of data, I usually find that I must "clean" the data.
That is, get rid of undesirable and unseen characters. Cell-View will tell
me what is in the cells, and Edit Replace will convert them to
"nothing".....or "ASAP Utilities" has a feature that will help.

hth
Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access and

Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them,

and
my count changes. So, can I get Excel to put a value into my "blank"

cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for

A,
3 for B and 1 for C. Any ideas?





--

Dave Peterson
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

Cell View is a free add-in from http://www.cpearson.com/excel/CellView.htm
"really" nifty.

Vaya con Dios,
Chuck, CABGx3


"Sloth" wrote in message
...
what do you mean by "Cell-View"?

"CLR" wrote:

When I do importing of data, I usually find that I must "clean" the

data.
That is, get rid of undesirable and unseen characters. Cell-View will

tell
me what is in the cells, and Edit Replace will convert them to
"nothing".....or "ASAP Utilities" has a feature that will help.

hth
Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access and

Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've

tested
this theory by highlighting a couple of "blank" cells and deleting

them,
and
my count changes. So, can I get Excel to put a value into my "blank"

cells,
so then I could filter it out, or create a formula that would only

count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2

for
A,
3 for B and 1 for C. Any ideas?






  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John K
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

I tried the cell-view but it showed the cell as blank, but counta would count
it anyway. This was data that I copied from an access table. But when I did
a find and replace and choose find all with nothing in the find it found all
the cells that were suppose to be blank. I then did the ctrl A and it
selected them. I then closed the find and replace then did a delete. Then
the counta worked. I don't know what access is putting in those blank cells,
but excel doesn't show anything but the counta doesn't see them as blank.
Maybe if someone from MS checkes these fourms they could have an answer. I'm
running office 2002 sp2

"CLR" wrote:

When I do importing of data, I usually find that I must "clean" the data.
That is, get rid of undesirable and unseen characters. Cell-View will tell
me what is in the cells, and Edit Replace will convert them to
"nothing".....or "ASAP Utilities" has a feature that will help.

hth
Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access and

Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them,

and
my count changes. So, can I get Excel to put a value into my "blank"

cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for

A,
3 for B and 1 for C. Any ideas?




  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Imported Data creates blank cells that aren't really blank

Try turning on:
Tools|Options|Transition tab|Transition navigation keys

Then look at the formula bar.

Do you see a single apostrophe.

If yes, I can tell you one way to get those using excel (I don't speak the
Access).

If you have formulas that evaluate to ""
=if(a1=1,"","nope")
(say)

And copy|paste special|values, then you're left with this detritus--and it's
usually invisible (who uses Lotus 123 transition settings???)

I use this to clear up this detritus:
Select the range to fix (all the cells if you want--it won't hurt the good
ones).

edit|replace
what: (leave blank)
with: $$$$$
replace all
(use a nice unique string that doesn't appear in any cell--don't include the
apostrophe, though!)

Then once more
edit|replace
what: $$$$$
with: (leave blank)
replace all

You'll see the =counta()'s will work ok--as well as the arrow keys.

Selecting a cell (say A1), then End, then Down arrow will stop where it's
supposed to.

====
ps. Don't forget to turn that transition setting back!


John K wrote:

I tried the cell-view but it showed the cell as blank, but counta would count
it anyway. This was data that I copied from an access table. But when I did
a find and replace and choose find all with nothing in the find it found all
the cells that were suppose to be blank. I then did the ctrl A and it
selected them. I then closed the find and replace then did a delete. Then
the counta worked. I don't know what access is putting in those blank cells,
but excel doesn't show anything but the counta doesn't see them as blank.
Maybe if someone from MS checkes these fourms they could have an answer. I'm
running office 2002 sp2

"CLR" wrote:

When I do importing of data, I usually find that I must "clean" the data.
That is, get rid of undesirable and unseen characters. Cell-View will tell
me what is in the cells, and Edit Replace will convert them to
"nothing".....or "ASAP Utilities" has a feature that will help.

hth
Vaya con Dios,
Chuck, CABGx3


"JackieD" wrote in message
...
Here's what I'm attempting to do: For each column, X,Y, Z, I am

attempting
to count nonblanks. However, the data was imported from Access and

Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them,

and
my count changes. So, can I get Excel to put a value into my "blank"

cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for

A,
3 for B and 1 for C. Any ideas?





--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
enter numbers in blank cells bill gras Excel Worksheet Functions 2 September 21st 05 01:17 PM
pivot tables reports - altering display of (blank) cells cak Excel Worksheet Functions 1 August 22nd 05 12:39 AM
Counting blank and filled cells within a range. greg7468 Excel Discussion (Misc queries) 3 June 28th 05 10:41 PM
trying to create an (almost) circular formula between cells and data validated cells with lists KR Excel Worksheet Functions 0 May 12th 05 07:21 PM
Autofill data in specific blank cells Mr. G. Excel Worksheet Functions 0 April 22nd 05 09:41 PM


All times are GMT +1. The time now is 09:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"