'Excel cells from Access export look empty but are not (=isblank=FALSE)
My problem is that an Excel spreadsheet (exported from Access as .xls) has cells that look empty, but are not. This is mucking up my calculations & navigation shortcuts.
There are no formulae or contents in the cells (the answers already posted on this topic don't fix my problem). I've attached an image of my problem (see bottom)
Troubleshooting shows:
If I test these cells e.g.
=isblank(a1)
, it's FALSE.The cell lengnth is 0, according to
=LEN(a1)
If I try 'Go to (special) highlight "Blanks" (or any other go to special combination like formula/text,numbers, etc) it will NOT highlight these empty looking cells
YET if I filter the column, I can select these non-empty "blanks" from the filter list (this is the only way to identify these tricky cells I've found so far) -
So my column has entries in some cells, "blank" non-empty cells. I only want the cells with entries, the rest I need cleared. This also annoyingly means the shortcut to skip to the next empty or nonempty cell wont work (it reads all as nonblank) - making it super painful to navigate the large dataset.
Once I click within an individual 'non-empty' blank cell & press enter, this seems to clear the cell contents ('=isblank
' formula's that were saying "FALSE" now switch to 'TRUE') - this is not feasible to fix individual cells in such a large dataset though.
Can ANYONE help?!
I have found 2 basic workarounds that fix this, but I really want to know how & why this happens & how to avoid it in future.
Workaround 1
In excel, filter the column, show only "blank", then highlight the filtered column & press delete. Unfilter the list & the problems solved.
Workaround 2
save the excel spreadsheet from 'file.xlsx' & save as '.csv'.
Close it all, open the csv & it seems the non-empty blank cells are fixed, show =isblank= TRUE
& can be skipped with [CNTL arrow key] shortcuts now.
This is so frustrating & I haven't seen any similar questions nor answers on why this is?
Why is this happening & are there any other fixes around for this? Thanks hive-mind! excel sheet shows non-blank empty cells - working
Solution 1:[1]
Sometimes it's nice to have the ctrl+up/down stop at the edge of the data set other times it's not; here's the macro I use to clear the selected range's "blank" cells for when it's not:
'clears cells with error or empty string values
Public Sub clearJunk()
Dim scrn As Boolean: scrn = Application.ScreenUpdating: Application.ScreenUpdating = False
Dim i As Long, rowCount As Long, FirstRow As Long
Dim col As Range
rowCount = Selection.Columns(1).Cells.count
FirstRow = Selection.Cells(1).Row - 1
For Each col In Selection.Columns
For i = 1 To rowCount
If IsEmpty(col.Cells(i)) Then i = col.Cells(i).End(xlDown).Row - FirstRow
If i > rowCount Then Exit For
If IsError(col.Cells(i).Value) Then
col.Cells(i).ClearContents
ElseIf col.Cells(i).Value = "" Then
col.Cells(i).ClearContents
ElseIf Trim(col.Cells(i).Value) = "" Then
col.Cells(i).ClearContents
End If
Next i
Next col
Application.ScreenUpdating = scrn
End Sub
Also if you're copying and pasting from MS-Access you might find this one useful too:
Public Sub UnWrapText(): Selection.WrapText = False: End Sub
I've bound both to buttons on my Ribbon/QAT and it's made my life more hassle free.
Solution 2:[2]
You can also try the below:
- Select your region.
- In the "Find & Replace" dialog box, leave the "Find what:" box empty.
- Enter any value that doesn't exist yet in your data, e.g. a pipe ("|") in the "Replace with:" box.
- Check the "Match entire cell contents" option.
- "Replace All"
- Now, enter the pipe in the "Find what:" box, and clear the "Replace with:" box.
- "Replace All", and you're done!
Source: https://www.mrexcel.com/board/threads/how-to-remove-null-string-from-cells.565955/ (A bit updated by me)
Solution 3:[3]
Just save you excel file as an csv file. Then create in Access a linked table to the csv file. When you create a query to import your data then values which look empty in excel will have a NULL value in Access.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Gregor y |
Solution 2 | Hary Dee |
Solution 3 | Michel Schellekens |