Struggling to acomplish something seemingly simple in MS Excel

[One]

My blank cells aren't truly blank! This causes a problem seeing as I have maybe 6000 rows of data, however some just hold a name in one column but no data in the columns next to it. I weed it out by using a simple 'IF(AND(' formula and thus get just the rows with data. However, when I then copy these columns and paste special (values) it turns out the blanks aren't blanks. This means I can't simply F5 select blanks and then delete.

The only option is to replace the 'blanks' with a string, then replace that string with blanks thus making them proper blanks.

So what I'm asking is why do I have to Ctrl-H my fake blanks into real blanks?

See the picture for a better explanation.

p.s. the numbers are those words' scrabble scores.

 

[One]

That's not really the issue, the ISBLANK is just there to demonstrate it's not blank. What I really want to do is (considering IRL i'll have over 6000 rows with blanks dotted all over the place) is highlight the columns press F5 then special, then select the blanks so I can delete them and be left with all the rows in the same order but without the blanks separating them. Obviously I can do this by using ctrl+h twice but surely I shouldn't have to do this? It must be possible to create a true blank with a formula no?

To the above poster: Assume the words have now changed into account numbers, it is impossible to differentiate a 0 from an account number. I suppose instead of "" or 0 i could just use an errror NA() and then I'd only have to replace #N/A with a blank, but having to use ctrl+h at all feels like a bit of a bodge! Maybe using a bodge is just what I'll have to do...

 

[One]

Well if they changed to account numbers I would use still use "" and then select text instead of blanks. The only problem would come is if you had a mixture of numbers and text strings in the list.

What would you do in this situation?

 

[One]

Couldn't he just bring it to a new page where only the lines containing 'False' are carried over negating the need to delete the rows.

This is a lot of hassle to simple remove rows that should be blank by using F5. I'll just use ctrl+h twice, it's not really a hassle I just assumed there would be a way to format a cell as blank (not a string of length 0) using an excel forumla, apparently there isn't though.

What I'm struggling to understand is for what purpose MS would chose to distinguish between a blank cell and a cell that is blank but a string.

 

[One]

Lol, you just don't get it do you.