Excel experts - skip blanks, "" and len?

[steford]

I have a big load of data that I have copied from a word doc. I can get to the bits I want by looking at a column (D) and if it contains something I grab cells B, C and D in that row. I'm using =IF(D1<>"",B1,"") for the B column for example - all good. So gives me 3 columns and several new "blank" rows as only about 1/10th of the original data rows are useful.

Now - I assumed I could just copy and paste special... this with "Skip Blanks" to get my data minus empty rows. Not so.

ISBLANK() on my "blank" cells returns FALSE but bizarrely LEN() on it returns absolutely nothing - not even 0. If I go into the cell and hit back space ISBLANK() returns TRUE. If I enter "aaa" for example LEN() returns 3 as expected.

What's going on? I thought "" was blank? How can I force a blank if this is not the case?

Thanks.

[sdl]

ISBLANK function probably doesn't work because the cell contains a formula (containing the IF function).

Try using IF function to test for blanks i.e.
=IF(CELL="",TRUE,FALSE)

Any reason why you can't copy and then paste special (with 'is blank' option) on the original data cells ?

[steford]

Thanks mate. ISBLANK returns FALSE on the copy and paste specialed values cells. So for some reason the IF doesn't return a blank and whn that is pasted as values it's still not blank. I then intended to paste special again and lose the blanks (or do it in 1 go). So testing for "" as above does indeed return true. So how can I make a cell blank via a function? eg IF(D1<>"",B1,BLANK) ?

My original data contains lots of non blank cells that I don't need.

[emeyedeejay]

ignore me - will read properly shortly :nuts:

[emeyedeejay]

The easiest way I've found to remove blanks is to sort this puts all the blanks together then I simply delete the rows I don't need.

This may / may not be useful to you depending on how your data is organised.

[steford]

OK done. Added a row() number alongside each row of good data then sorted that ascending in another sheet to maintain original row order.

Thanks a lot.