+ Reply to Thread
Results 1 to 21 of 21

Eliminate Blank Cells

  1. #1
    Registered User
    Join Date
    06-03-2009
    Location
    christiansburg, va
    MS-Off Ver
    Excel 2007
    Posts
    20

    Eliminate Blank Cells

    I have a spreadsheet with data in all different rows.
    I'm trying to delete all the blank cells in between.
    I tried go to special, blank, and delete cells,
    but for some reason, the excel doesn't select all the blank cells.
    It only selects a few blank cells, which is really strange.

    Does anybody know what could be the problem?

    or if someone would take a look at my spreadsheet and help me out.

    Thanks a lot!

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Eliminate Blank Cells

    Hi,

    Sounds like they are not actually blank, select one and try this formula

    =ISBLANK(A1) where A1 appears to be blank but isn't will return FALSE
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Eliminate Blank Cells

    Also u can try

    1. Perss F5
    2. Special> Blank >OK (it would select blank cells within the range)
    3. RightClick>Delete (CTRL+"-")
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  4. #4
    Registered User
    Join Date
    06-03-2009
    Location
    christiansburg, va
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Eliminate Blank Cells

    Hi, thanks for your reply.
    A1 is not blank.
    I tried this formula in a different cell.
    =ISBLANK(B6) and it turned 0

    So, does it mean that cell is blank or is it not?

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Eliminate Blank Cells

    If it returns 1=TRUE, 0=FALSE

  6. #6
    Registered User
    Join Date
    06-03-2009
    Location
    christiansburg, va
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Eliminate Blank Cells

    Quote Originally Posted by ContaminatedWitExcel View Post
    Also u can try

    1. Perss F5
    2. Special> Blank >OK (it would select blank cells within the range)
    3. RightClick>Delete (CTRL+"-")

    Hi, Thanks for the suggestion. I just tried it.
    It is just like when I did , Find&Select, Go to Special, Blank, Ok, and Delete Cells. It didn't select all the blank cells.

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Eliminate Blank Cells

    Why not post the workbook or part of it so we can see the problem?

  8. #8
    Registered User
    Join Date
    06-03-2009
    Location
    christiansburg, va
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Eliminate Blank Cells

    Ok, so that means these cells that it didn't select are not truely blanks?

    They sure appear to be blank.

    Is there anyway to fix it?
    It's a large data with 10,000+ rows.
    I dont know what to do if I can't delete all the blank cells.

  9. #9
    Registered User
    Join Date
    06-03-2009
    Location
    christiansburg, va
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Eliminate Blank Cells

    Quote Originally Posted by oldchippy View Post
    Why not post the workbook or part of it so we can see the problem?
    Thanks soooo much!

    But I have a dumb question,
    how can I post the file on here?

  10. #10
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Eliminate Blank Cells

    Like I said let's see a part of it?

  11. #11
    Registered User
    Join Date
    06-03-2009
    Location
    christiansburg, va
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Eliminate Blank Cells

    Quote Originally Posted by oldchippy View Post
    Like I said let's see a part of it?
    Oops..I didn't know there's an additional option that I can post attachments. lol

    Okay. So the attachment is part of the file that i'm working on.

    I'm working on Sheet2 which I copied and pasted from Sheet1.

    Please take me look at it for me.

    Thank you!
    Attached Files Attached Files

  12. #12
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Eliminate Blank Cells

    Ok
    try this one. Take a look at this pic.
    Last edited by contaminated; 06-29-2009 at 05:02 PM.

  13. #13
    Registered User
    Join Date
    06-03-2009
    Location
    christiansburg, va
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Eliminate Blank Cells

    Quote Originally Posted by ContaminatedWitExcel View Post
    Ok
    try this one. Take a look at this pic.
    Thanks for the suggestion.

    Excel says can't find what i'm searching for when I type double quotation mark.

  14. #14
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Eliminate Blank Cells

    If you look on sheet1 you have some formulas, fro example in C8

    =IF(LEFT(A8,4)="Name",MID(A8,FIND(" ",A8)+2,20),"")

    The problem is this is putting a space in the cell - therefore it isn't blank, it needs to be

    =IF(LEFT(A8,4)="Name",MID(A8,FIND("",A8)+2,20),"")

  15. #15
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Eliminate Blank Cells

    In fact if you do a FIND/REPLACE for " " to replace with "" and check look in Formula's there are over 26000 of them

  16. #16
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Eliminate Blank Cells

    I've got it
    Leave FindWhat field empty. I mean just press Ctrl+F and press Find All. I've got it workin'

  17. #17
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Eliminate Blank Cells

    i cant see a difference there old chippy ! but if you do len(f10) it comes up 0
    but i can see a single ' in the cell
    Last edited by martindwilson; 06-18-2009 at 04:38 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  18. #18
    Registered User
    Join Date
    06-03-2009
    Location
    christiansburg, va
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Eliminate Blank Cells

    Quote Originally Posted by oldchippy View Post
    If you look on sheet1 you have some formulas, fro example in C8

    =IF(LEFT(A8,4)="Name",MID(A8,FIND(" ",A8)+2,20),"")

    The problem is this is putting a space in the cell - therefore it isn't blank, it needs to be

    =IF(LEFT(A8,4)="Name",MID(A8,FIND("",A8)+2,20),"")

    Wow..that's the problem.
    Thank you so much.
    You solved my problem.

  19. #19
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Eliminate Blank Cells

    ok you've all lost me changing to
    =IF(LEFT(A8,4)="Name",MID(A8,FIND("",A8)+2,20),"")
    from
    =IF(LEFT(A8,4)="Name",MID(A8,FIND(" ",A8)+2,20),"") means it wont find the space and the mid function will be wrong surely?
    result 2 gives
    "3631 U. S. Highway #90-East"
    formula 1 gives
    "ne 1 3631 U. S. Highway #90-East"

  20. #20
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Eliminate Blank Cells

    Whoops - have I got it wrong, I must admit I didn't check first - Yes I did - sorry!
    Last edited by oldchippy; 06-18-2009 at 04:48 PM.

  21. #21
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Eliminate Blank Cells

    what i can understand is...insert a new sheet
    select A1 hit delete just in case make sure cell formated general (just in case again)
    isblank A1 shows true.. .....expected
    len(a1) =0 ....expected
    formula bar shows nothing visible....expected
    now go copy a cell with a formula in that looks blank
    and paste special values into a1 of new sheet
    isblank(a1) turns to false
    len(a1) still = 0 though
    and in the formula bar i can see a single ' which i can delete with delete or clicking in formula bar and using backspace isblank(a1) now goes back to true
    but i cant do it by find/replace nothing
    copy cell into find replace either
    and code(a1)shows #VALUE!

+ 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