Apparently empty (blank) cells aren't empty
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Apparently empty (blank) cells aren't empty
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Apparently empty (blank) cells aren't empty

    I exported an access database to an excel spreadsheet. In one of my columns, I need to select or 'go to' all blank fields so that I can then copy data from above field into them. Problem is, the fields (shown below) appear blank, but they aren't. So when I select the column and choose 'Edit | Go To | Special | Blanks | Ok', no blank fields are recognized.


    -- removed inline image ---


    I read on another site's thread that possibly when exporting empty fields in access file to excel, there is in fact a null string there rather than it actually being empty and this may be spaces.

    I am hoping there's a way to possibly identify the true contents of these supposedly blank cells and then to truly empty/clear the contents. Or, I could find all cells that have these invisible characters and 'go to' them & then do my copy step. Any ideas?

    Thanks, John
    Last edited by daymaker; Mar 8th, 2012 at 01:39 PM.

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,069
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    Use formulas like LEN top identify if a cell is blank
    and CODE to identify the ASCII character within the cell

  3. #3
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    Hi,

    select the range and run this code
    Code:
    Sub test()
    With Selection
    .Value = .Value
    End With
    End Sub
    does that work for you?

    kind regards,
    Erik
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  4. #4
    New Member
    Join Date
    Feb 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    Erik, can you tell me what I need to do to 'run code'? Is this under 'Record Macro' or possible Visual Basic Editor?

  5. #5
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
    On the Insert menu in the VBE, click Module. In the module (the white area at the right), paste your code:

    to run the code
    click anywhere in the code and hit function key F5
    or
    via Excel menu: Tools / Macro / Macros (or hit Alt+F8)
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  6. #6
    New Member
    Join Date
    Feb 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    Awesome, Erik. That worked. Thanks a lot!

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,666
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    Quote Originally Posted by Special-K99 View Post
    Use formulas like LEN top identify if a cell is blank
    Hi Special-K99

    Remark: Len() will only tell you if the cell has characters, not if it's empty, for that you must use IsBlank(). In this case, since Erik's code solved the problem, the cells would have empty strings. Since the cells were not empty the Go To Blanks would not work.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #8
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    I can't remember if there was any manual solution for this without code.
    Do you, Pedro?
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  9. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,666
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    Hi Erik

    If the cells are in the same column, I usually do: select the cells, choose Fixed Width and press directly Finish in the first panel.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,666
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Apparently empty (blank) cells aren't empty

    Quote Originally Posted by pgc01 View Post
    Hi Erik

    If the cells are in the same column, I usually do: select the cells, choose Fixed Width and press directly Finish in the first panel.
    Sorry, as I'm sure you guessed, I meant: select the cells, invoke Text to Columns and press directly Finish in the first panel
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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