Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-08-2012, 11:41 AM
daymaker daymaker is offline Apparently empty (blank) cells aren't empty Windows XP Apparently empty (blank) cells aren't empty Office 2000
Novice
Apparently empty (blank) cells aren't empty
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default Apparently empty (blank) cells aren't empty

I exported an access database to an excel spreadsheet. In one of my columns, I need to select or 'go to' all blank fields so that I can then copy data from above field into them. Problem is, the fields (shown below; actual file attached as well) appear blank, but they aren't. So when I select the column and choose 'Edit | Go To | Special | Blanks | Ok', no blank fields are recognized.





I read on another site's thread that possibly when exporting empty fields in access file to excel, there is in fact a null string there rather than it actually being empty and this may be spaces.

I am hoping there's a way to possibly identify the true contents of these supposedly blank cells and then to truly empty/clear the contents. Or, I could find all cells that have these invisible characters and 'go to' them & then do my copy step. Any ideas?

Thanks, John
Attached Files
File Type: xls Blanks not blank.xls (13.5 KB, 2 views)
Reply With Quote
  #2  
Old 03-08-2012, 12:44 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Apparently empty (blank) cells aren't empty Windows XP Apparently empty (blank) cells aren't empty Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,398
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

As you suspected your cells contain null strings
If you enter
Code:
=istext(b2)
and copy down the result is TRUE for the empty cells.
So, you can select these cells, click "delete".
They are now blank cells
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #3  
Old 03-08-2012, 02:03 PM
daymaker daymaker is offline Apparently empty (blank) cells aren't empty Windows XP Apparently empty (blank) cells aren't empty Office 2000
Novice
Apparently empty (blank) cells aren't empty
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default

I failed to mention that the actual spreadsheet I'm wanting to select and delete null strings from is 1,947 records long with actual $$ amounts intermittently sprinkled throughout. Are there any shortcuts rather than manually selecting/highlighting all of these cells?
Reply With Quote
  #4  
Old 03-08-2012, 03:41 PM
daymaker daymaker is offline Apparently empty (blank) cells aren't empty Windows XP Apparently empty (blank) cells aren't empty Office 2000
Novice
Apparently empty (blank) cells aren't empty
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default

Issue resolved; I was able to select my range and run this code:

Sub test()
With Selection
.Value = .Value
End With
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Empty cells plotted as zero. ColinC Excel 2 01-23-2012 08:02 AM
Apparently empty (blank) cells aren't empty Vba, Is there a better way to test empty cells? Hwyn Excel Programming 2 11-14-2011 11:44 AM
Apparently empty (blank) cells aren't empty Empty rules rene.kamphuis@ciz.nl Mail Merge 21 04-15-2011 12:34 AM
Shared Calendar Empty worksmart Outlook 0 01-21-2011 06:32 AM
Populate an empty .PAB file imported_contiw Outlook 2 08-17-2006 08:50 PM


All times are GMT -7. The time now is 05:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft