Apparently empty (blank) cells aren't empty

daymaker

New Member
Joined
Feb 28, 2012
Messages
15
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) 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.


-- removed inline image ---


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
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Use formulas like LEN top identify if a cell is blank
and CODE to identify the ASCII character within the cell
 
Upvote 0
Hi,

select the range and run this code
Code:
Sub test()
With Selection
.Value = .Value
End With
End Sub
does that work for you?

kind regards,
Erik
 
Upvote 0
Erik, can you tell me what I need to do to 'run code'? Is this under 'Record Macro' or possible Visual Basic Editor?
 
Upvote 0
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. In the module (the white area at the right), paste your code:

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)
 
Upvote 0
Use formulas like LEN top identify if a cell is blank

Hi Special-K99

Remark: Len() will only tell you if the cell has characters, not if it's empty, for that you must use IsBlank(). In this case, since Erik's code solved the problem, the cells would have empty strings. Since the cells were not empty the Go To Blanks would not work.
 
Upvote 0
Hi Erik

If the cells are in the same column, I usually do: select the cells, choose Fixed Width and press directly Finish in the first panel.
 
Upvote 0
Hi Erik

If the cells are in the same column, I usually do: select the cells, choose Fixed Width and press directly Finish in the first panel.

Sorry, as I'm sure you guessed, I meant: select the cells, invoke Text to Columns and press directly Finish in the first panel
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top