Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
gjcase
 
Posts: n/a
Default 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...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=394016

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=394016


--

Dave Peterson
  #3   Report Post  
gjcase
 
Posts: n/a
Default


Thanks, Dave. The use of N/A is a good trick to remember.


--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=394016

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Append Text to Cell Values Using Replace Ngan Excel Discussion (Misc queries) 4 June 4th 05 08:30 PM
conditional formating for a blank cell wsoung Excel Discussion (Misc queries) 5 March 9th 05 11:15 PM
conditional formatting blank cell TREK5200 Excel Discussion (Misc queries) 1 December 6th 04 03:23 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 05:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"