![]() |
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 |
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 |
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 |
All times are GMT +1. The time now is 09:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com