Replace null string with blank cell

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'm 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. 

GJCase


-- 
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26061
View this thread: http://www.excelforum.com/showthread.php?threadid=394016

0
8/8/2005 8:24:26 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
524 Views

Similar Articles

[PageSpeed] 9

Saved from a previous post:

After you convert to values, you can see that those "empty" cells aren't really
empty.

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.

gjcase wrote:
> 
> 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'm 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.
> 
> GJCase
> 
> --
> gjcase
> ------------------------------------------------------------------------
> gjcase's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26061
> View this thread: http://www.excelforum.com/showthread.php?threadid=394016

-- 

Dave Peterson
0
petersod (12005)
8/8/2005 9:13:30 PM
Thanks, Dave.  The use of N/A is a good trick to remember

--
gjcas
-----------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2606
View this thread: http://www.excelforum.com/showthread.php?threadid=39401

0
8/9/2005 1:13:19 PM
Reply:

Similar Artilces: