Mystery blank cells

[sgoyal]

I have a spreadsheet with many seemingly blank cells. However, "F5 (Go to) | Special | Blanks" does not select any of them. I also tried the ISBLANK function on some of them and excel (2010) returns FALSE. I want to delete the content of these cells. Please help. Thanks.

Sameer

[tom1977]

Hi
Could You send sample workbook? Check color of font (maybe you have conditional formating with white color of font)

[JieJenn]

It's called NULL. Kinda like when you press the Space Key and Excel recognize "the space" as a value. If you delete the cell, Isblank() should give you "TRUE" instead of False.

[ChemistB]

Several possibilites
The one tom mentioned above (values are whited out so to speak)
You have spaces in those cells " ". Go to cell and see if the cursor will move around in the formula bar.
You have other invisible characters. In another blank cell, enter = CODE(A1) where A1 is the cell you're investigating. See if a number comes up.
You have a null string in those cells "" These can be left over in cells where there used to be formulas. Best way to get rid of these is Data> Text to Columns>Finish

Hope that helps.

[sgoyal]

It's called NULL. Kinda like when you press the Space Key and Excel recognize "the space" as a value. If you delete the cell, Isblank() should give you "TRUE" instead of False.

The seemingly blank cells are spread out over the entire spreadsheet. I would have to navigate to each cell and then press delete. I would like to select these cells all at once and then press delete. Thanks.

[ChemistB]

Are they interspersed with your data or after your data?
If after, select the last row after your data, CNTRL SHIFT Down Arrow to select all remaining rows
Right click and Delete
Do the same with the last column after your data with right arrow.
Save and close the workbook, then reopen and all those cells will be blank

[sgoyal]

Are they interspersed with your data or after your data?
If after, select the last row after your data, CNTRL SHIFT Down Arrow to select all remaining rows
Right click and Delete
Do the same with the last column after your data with right arrow.
Save and close the workbook, then reopen and all those cells will be blank

The cells are interspersed with the data

[martindwilson]

what does =CODE() return for any of those cells?

[sgoyal]

what does =CODE() return for any of those cells?

CODE() results in a #VALUE! error

[shg]

Maybe post a workbook ...

[sgoyal]

Here's a column from the spreadsheet with the "blank" cells.

[JieJenn]

Try

Please Login or Register  to view this content.

[ChemistB]

As per post 4 "Best way to get rid of these is Data> Text to Columns>Finish"
Although your data is currently stored as text. Text to Columns>Finish will convert them to numbers.
If you want them to stay as text
After clicking on "Text to Columns" go "Next", "Next" then click the "Text" radio button and "Finish"

You can only do 1 column at a time this way but certainly much faster than cell by cell
Does that help?

Alternately, I'm sure a macro could be written to go through your range and clean up those "blank" cells.
EDIT: Ha, JieJenn already beat me to it!

[JieJenn]

As per post 4 "Best way to get rid of these is Data> Text to Columns>Finish"
Although your data is currently stored as text. Text to Columns>Finish will convert them to numbers.
If you want them to stay as text
After clicking on "Text to Columns" go "Next", "Next" then click the "Text" radio button and "Finish"

You can only do 1 column at a time this way but certainly much faster than cell by cell
Does that help?

Alternately, I'm sure a macro could be written to go through your range and clean up those "blank" cells.
EDIT: Ha, JieJenn already beat me to it!

If you do come up with a solution it probably would be 3x more efficient than mine.

[Marcol]

This thread I started some time ago has some examples and comments that might be of interest to you.

What is in these cells?

[sgoyal]

Thanks JieJenn. The macro works flawlessly.

[sgoyal]

Yes. Text to Columns works, even if a column at a time. Thanks for the help!