How to replace a null string with blank cell in Microsoft Excel

[Eas!war]

I have a large table which was created via lookups from other tables. Many of the entries are (or should be) blank. After the table was created, I converted everything to values (Paste Special/Values) in order to save memory. However, the table now contains a null string or other unprintable character in each of the "blank" cells. Thus, if I use END-DN or END-UP to find the next value in the table, the cursor goes to the end of the table, as it sees something in each cell. If I edit a "Blank" cell, I see no characters, and if I select the formula bar & hit ENTER, the cell becomes truly blank.

I realize this is similar to the issue MJ had a few weeks ago, in fact I found this forum via a Google search which turned up that thread. None of the methods proposed there appear to address the issue of making a blank cell truly blank. I am hoping some of you have a method to do this short of selecting the cells & deleting the null strings manually, as the spreadsheet is much too large to do this way.

[AnitG]

After you convert to values, you can see that those "empty" cells aren't really empty. Click on Tools > Options > Transition tab > check Transition navigation keys (remember to toggle it off later!)/ Now look at the formula bar for one of those empty cells--you'll see a single quote. I usually use a formula like: =if(a1=0,"ok",na()) (change your formula to return #n/a! instead of "".) convert to values and then do that Edit|replace on the #n/a! string.

[Shurod]

I would like if possible to be able to do my between the same but with a bogus date 9999-01-01 genre to place my request that I return anything because it is a null in the dated. You want your return value is set to a default date if its value is NULL/ If this is the case, you have the SQL function "IFNULL" (or ISNULL for MSSQL) to use as follows: SELECT IFNULL (field_name, 'value_replacement_is_NULL') FROM table_name. It has the effect of replacing NULL with a default value when returning data.