Remove Unprintable Non-Blank Cells Having Zero Length Contents

[aaronb]

Hi,

I'm importing data into a spreadsheet and what are supposed to be blank cells actually aren't. ISBLANK returns false. CODE(cellref) returns #VALUE error -- in the calculation steps, it looks like CODE( ""). TRIM(cellref) doesn't return a blank cell. Neither does CLEAN(cellref).

"Clear contents" works.

It was imported from a csv.

Any ideas?

[patel]

attach please a sample file

[aaronb]

Sample workbook attached

Whitespace Char.xlsx

Edit: The cell didn't copy correctly -- ISBLANK returns TRUE. Let me try to get a disfunctional one up.

Sorry about that, here's a proper copy. The messed-up cell is A1.

Non-blank Cell.xlsx

[patel]

I did not understand your goal

[aaronb]

I did not understand your goal

Macro to get rid of that character in the workbook so the affected cells return true for an ISBLANK check.

[Rick Rothstein]

Hi,

I'm importing data into a spreadsheet and what are supposed to be blank cells actually aren't. ISBLANK returns false. CODE(cellref) returns #VALUE error -- in the calculation steps, it looks like CODE( ""). TRIM(cellref) doesn't return a blank cell. Neither does CLEAN(cellref).

"Clear contents" works.

Give this a try and see if it fixes your problem. Select all the cells on the worksheet, then press CTRL+H to bring up Excel's Replace dialog box. If there is anything in the "Find what" field, remove it, then click into the "Find what" field to place the text cursor in it, then press and hold down the ALT key and, while it is down, type the numbers 0160 using the NUMBER PAD only (it will not work if you use the main keyboard to input those digits). Make sure there is nothing in the "Replace with" field. Click the "Options>>" button and make sure the "Match entire cell contents" checkbox does NOT have a check mark in it, then click the "Replace All" button. See if you can do whatever you want/need with those cells now.

[Rick Rothstein]

Macro to get rid of that character in the workbook so the affected cells return true for an ISBLANK check.

If you want a macro, then check out the code I posted in this message in different forum...

http://www.excelfox.com/forum/f13/tr...-155/#post1092

[aaronb]

Give this a try and see if it fixes your problem. Select all the cells on the worksheet, then press CTRL+H to bring up Excel's Replace dialog box. If there is anything in the "Find what" field, remove it, then click into the "Find what" field to place the text cursor in it, then press and hold down the ALT key and, while it is down, type the numbers 0160 using the NUMBER PAD only (it will not work if you use the main keyboard to input those digits). Make sure there is nothing in the "Replace with" field. Click the "Options>>" button and make sure the "Match entire cell contents" checkbox does NOT have a check mark in it, then click the "Replace All" button. See if you can do whatever you want/need with those cells now.

Rick,

Unfortunately that didn't work. It did return 4 matches, but not the cells in question.

[patel]

attach please a new file again with data and desired result

[Rick Rothstein]

attach please a new file again with data and desired result

I do not see any attachment.