Delete random blank Excel cells and shift remaining cells left to fill the gaps

I love learning new things. Today I was using a CSV export of data in Excel and was just using Excel to reassemble the data. At one point it resembled something like:

This is, of course, after some manipulation. Basically I removed the parts of a URL column expanded by delimiter (“/”) I didn’t want. This left awkward gaps and all I wanted was to quickly delete the gaps and shift contents of remaining cells to the left to sort of “reconstruct” each row without gaps. Since they didn’t line up in neat columns, I needed a different method.

I found a great article that helped. To summarize the steps:

  1. Select the range for which you’ll delete blank cells and shift data left.
  2. Press Ctrl+G.
  3. Click Special… (lower left of dialog)
  4. Choose the Blanks radio button
  5. Click OK.
  6. All blank cells in the selected range remain highlighted. Now right-click any of the selected blank cells.
  7. Choose Delete.
  8. Select Shift cells left.
  9. Click OK.

Here’s an animated GIF showing the process on my example data:

Click to enlarge

Leave a Reply

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