4

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

Community
  • 1
  • 1
anna davies
  • 41
  • 1
  • 3
  • Use =A1="" Or =IF(A1="","Blank","Data Present In Cell") – Sixthsense Aug 17 '17 at 06:44
  • Ooo, that works! Thank you sixthsense! This affects a different column though. Any way to clear the cells directly within a column (I know I can just filter col A, according to whether col B says 'blank' or 'data present') - either before exporting from access, or within excel? – anna davies Aug 17 '17 at 06:53
  • In ms-access you can use Filter (in particular column) by selecting Isnull as condition which will show the blank cells. In excel you can use the suggested formula in a helper column to know the actual blanks or you can use auto filter in excel also and select blank from the drop down which will identify the actual blanks (not like isblank() function). – Sixthsense Aug 17 '17 at 06:59
  • Also try the `CLEAN` function - https://support.office.com/en-us/article/CLEAN-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 - e.g. `=IF(ISBLANK(CLEAN(A1)),"blank","not blank")` – Robin Mackenzie Aug 17 '17 at 10:45

3 Answers3

0

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.

Gregor y
  • 1,762
  • 15
  • 22
0

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)

Hary Dee
  • 21
  • 7
0

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.

Michel Schellekens
  • 1