Fix Blank Excel Cells Copied From Database

Fix Blank Excel Cells Copied From Database

When you copy data to Excel, from another application, blank cells in the data can cause problems. Everything looks okay, at first glance, but the database blank cells don’t behave like other blank cells in the workbook. See how to fix blank Excel cells copied from a database, or created within Excel.

[Updated Jan. 23, 2018: More solutions to the problem, how to see hidden data, detailed steps]

Blank Cells Are Counted

In this example, there is a small table, copied from Access, with 3 cells – C4, C5 and C7 – that appear blank. In the Access database, those cells might have had a zero length string or a null value.

  • If you click on one of the “blank” cells, nothing shows in the Formula Bar.
  • However, the COUNTA formula in cell E2 is counting those cells, as if they contained data.

See more COUNTA examples, and other count functions on my Contextures site.

FixBlankCells01

Navigation Problem with Blank Cells

In addition to the COUNTA problem, there is a navigation problem with these cells that look empty.

  • Select the first cell in the column with the blank cells
  • Press the End key, and then the Down arrow
  • Normally, Excel goes down to the last cell that contains text
  • In this case though, Excel “sees” something in all the cells, and goes to the last cell in our list (C8)

See the Hidden Contents

You can see apostrophes in those “non-blank” converted cells, if you turn on an Excel option:

  • On the Excel Ribbon, click the File tab
  • At the left, click Option
  • In the Category list, click Advanced
  • Scroll down to the end of the Advanced options, and look for the Lotus Compatibility section
  • Add a check mark to Transition Navigation Keys

Transition Navigation Keys

  • Click OK, to close the Options window
  • Select a cell that looks blank, and check the Formula bar

You should see an apostrophe there.

apostrophe in formula bar

Other Causes for Blank Cells Problem

This problem can also occur WITHOUT importing data from Microsoft Access.

Follow these steps to create a similar situation with Excel formulas.

  • In column A, enter the numbers 1 to 10
  • In column B, enter this formula: =IF(ISODD(A2),1,””)
    • The formula checks the number in column A.
    • If it is odd, the IF function returns a 1.
    • If it is not odd, IF returns an empty string (“”)
  • The rows with 2, 4, 6, 8 and 10 look blank, because the formula returns an empty string (“”) in those cells

Fortunately, you can see the formula if you click on any of those cells, so it’s easy to see why the cells are not really blank.

cells with empty string formula

Change Formulas to Values

Next, follow these steps to create the blank cells problem:

  • Select all the cells with formulas
  • Copy the cells, and then Paste As Values, in the same location

Now those cells for 2, 4, 6, 8 and 10 look empty, but Excel still counts them in the COUNTA formula, in cell D3. And, if you click on any of the empty string cells, the formula bar shows nothing.

So, you can create the same issue by pasting Excel formulas as values, if some of the cells contain an empty string

empty string cells look blank

Fix Blank Excel Cells – Find and Replace

To fix the problem, so the “blank” cells are recognized as blank, you can use the Find and Replace command, in two steps.

NOTE: You can record a macro while you fix the cells, and run that macro later, if you encounter a similar problem. See my macro code, below.

Step 1 to Fix Blank Cells

  • Select all the cells
  • Press Ctrl + H to open the Find and Replace window
  • Leave the Find What box empty
  • In the Replace With box,type a string that is not in the data, such as “$$$$”
  • Click the Replace All button, to Replace all the blanks with $$$$

FixBlankCells02

Step 2 to Fix Blank Cells

  • Next, put “$$$$” in the Find What box
  • Press the Tab key, to go the the Replace With box
  • With the $$$$ text selected, press the Delete key, to clear the Replace With box
  • Click the Replace All button, to replace all the $$$$ entries with nothing

FixBlankCells03

Fix Blank Excel Cells – Text to Columns

Here is another quick solution, posted by Ed Ferraro, in the comments below.

  • Select all the cells
  • On the Excel Ribbon, click the Data tab
  • Click the Text to Columns command
  • In Step 1, select Delimited, then click the Finish button

That solves the problem, and you could record those steps as a macro too.

Fix Blank Excel Cells – Filter

Here is another quick solution, posted by Gobish, in the comments below.

  • In the column heading cell, click the arrow, to see the filter options
  • Remove the check mark from (Select All)
  • Add a check mark to (Blanks)
  • Click OK

filter to find blank cells

  • Select all the visible blank cells, and press the Delete key to clear them
  • To remove the filter, click the arrow in the heading cell, and click the Clear Filter command

That technique also solves the problem, and you could record those steps as a macro too.

The Fix Blank Cells Macro Code

Here is the Excel VBA code that fixes the blank cells, by using the Replace command. It works on the selected cells in the active worksheet.
Store the macro code on a regular worksheet module.

Sub FixBlankCells()
' Make blank cells from database really blank
    With Selection
        .Replace What:="", _
            Replacement:="$", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
        .Replace What:="$", _
            Replacement:="", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
    End With
End Sub

Watch the Fix Blank Cells Video

To see the steps for fixing the blank cells, and recording and running a macro, you can watch this short Excel video tutorial.

Download the Fix Blank Excel Cells Workbook

To work with the sample tables, and run the macro, you can download the Fix Blank Cells in Excel sample workbook, from the Excel Count Function Examples page on my Contextures site..

The file is in xlsm format, zipped, and contains macros. You’ll have to enable macros to test the Fix Blank Cells code in the sample file.
__________

19 thoughts on “Fix Blank Excel Cells Copied From Database”

  1. I like using Text to Columns to get rid of blanks. Just select a column, click Text to Columns, check delimited, and click Finish.

  2. I use Auto filter to filer for blank cells and then delete the contents of the blank cells. Normally this works fine for me. Thanks for the Find and replace option, i have not tried it that way.

  3. Debra, this is a nice little macro for cleaning up blank cells. I like it because this macro can be part of an excel automation solution that pulls data from a database, loads data into an Excel report, cleans it with your macro and then distributes the final report to users. Thanks for the helpful tip!

  4. You would think that since Microsoft makes both products they would have fixed this noxious problem. It can be a real pain in the you-know-what the first time you come across it. Thanks for the quick and efficient solution.

  5. I found that the following code works equally well (but probably not as fast):

    Sub FixBlankText()
    Dim cel As Range
    For Each cel In Selection
    If Len(cel) = 0 And VarType(cel) = vbString Then
    cel = 0
    cel = “”
    End If
    Next cel
    End Sub

  6. This was the best, simple, fast, elegant solution I’ve found so far. We don’t need any fancy coding. The simple, the better. Thank you, whoever you are!

  7. I have a hidden workbook that loads whenever Excel starts where I keep my frequently used macros (and I put them in a custom toolbar). I just select the range that might have blanks, and run my TrimAll macro:
    Sub TrimAll()
    Dim cell As Range
    MsgBox “This macro only trim SELECTED cells.”
    Application.ScreenUpdating = False
    For Each cell In Selection
    If cell.HasFormula = False Then
    cell.Value = Trim(cell)
    End If
    Next cell
    Application.ScreenUpdating = True
    MsgBox “Done!”
    End Sub

  8. Debra,
    “Fix Blank Excel Cells – Find and Replace” worked great! I have been researching how to fix this problem all day with no luck, until now. Thank you very much.

  9. Hi, I want to count only cells that contain visible data, I don’t want to count formulas that evaluate to a “”. I want to use this to set a dynamic print area. Any tips?

  10. Thanks for this detailed set of options. Your writing was simple, clear and easy to follow. Made my work easy and solved a problem. Wish you luck!

    Nevill

Leave a Reply to Nacho Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.