+ Reply to Thread
Results 1 to 15 of 15

Imported Data creates blank cells that aren't really blank

  1. #1
    JackieD
    Guest

    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. #2
    bj
    Guest

    RE: 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. #3
    JackieD
    Guest

    RE: 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. #4
    CLR
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #5
    CLR
    Guest

    Re: Imported Data creates blank cells that aren't really blank

    Try using COUNT instead of COUNTA.....

    Vaya con Dios,
    Chuck, CABGx3


    "JackieD" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #6
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    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

  7. #7
    Ron Coderre
    Guest

    RE: 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
    Edit>Find
    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. #8
    JackieD
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #9
    CLR
    Guest

    Re: Imported Data creates blank cells that aren't really blank

    COUNT

    Vaya con Dios,
    Chuck, CABGx3


    "JackieD" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #10
    JackieD
    Guest

    RE: 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
    > Edit>Find
    > 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. #11
    Sloth
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #12
    Dave Peterson
    Guest

    Re: 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #13
    CLR
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #14
    John K
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #15
    Dave Peterson
    Guest

    Re: 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1