Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Getting Rid of Spaces in Cells.

Getting Rid of Spaces in Cells

Written by Allen Wyatt (last updated March 18, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

Carole imports information into Excel from a different program, and this often leaves extra spaces in some cells. The spaces are the only things in the cells, so they appear to be empty but really aren't. Carole wondered about the best way to get rid of these unnecessary spaces.

There are a couple of approaches you can use. The first is to use the Find and Replace capabilities of Excel. Follow these steps:

  1. Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.
  2. In the Find What box, enter two spaces.
  3. Make sure the Replace With box is empty.
  4. Select the Match Entire Cell Contents check box.
  5. Click on Replace All.
  6. Continue clicking on Replace All until Excel finds no more matches.
  7. Repeat steps 2 through 5, but this time use only one space in step 2.
  8. Close the Find and Replace dialog box.

Another option is to use the Trim worksheet function. This approach is handy if the cells you want to modify are all in a particular area of the worksheet, such as a single column. For instance, if you want to get rid of the spaces from the cells in column D, you could use the following formula:

=Trim(D1)

The Trim function returns the contents of cell D1 without any leading or trailing spaces. You could then copy the results of this formula and use Paste Special to paste the values back into whatever cells you desire.

Of course, if you have lots of worksheets you need to process, or if you routinely get workbooks that contain the extra spaces in cells, a better way would be to create a macro that could get rid of the spaces. Perhaps the fastest way would be to examine all the cells in the worksheet and get rid of any extra spaces:

Sub CleanSheet1()
    For Each cell In ActiveSheet.UsedRange
        cell.Value = Trim(cell)
    Next cell
End Sub

The macro steps through each cell and uses the Trim function to get rid of any leading or trailing spaces. This works on all the cells, but it may produce undesired results, depending on the characteristics of your data. If you have cells that have leading spaces—and you want those spaces—then you'll need to use a different macro. This version will give more satisfactory results:

Sub CleanSheet2()
    Dim rCell As Range
    Dim rText As Range

    Set rText = Cells.SpecialCells( _
      xlCellTypeConstants, _
      xlTextValues)
    For Each rCell In rText
        If Trim(rCell.Value) = "" Then
            rCell.ClearContents
        End If
    Next
    Set rText = Nothing
    Set rCell = Nothing
End Sub

It only checks those cells containing constants (which includes all text in the worksheet) and then checks to see if using the Trim function would result in an empty cell. If so, then the cell is cleared. If the Trim function wouldn't result in an empty cell, then no change is made to the cell.

There is an important thing to keep in mind when it comes to using a macro-based approach. The VBA Trim function produces different results than the Trim worksheet function. Whereas the Trim worksheet function removes all double spaces throughout cell contents (including between words), the VBA Trim function does not—it only removes spaces at the beginning and end of a string.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12471) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Getting Rid of Spaces in Cells.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating a TOC that Includes Specific Styles

Want to create a special TOC that contains different elements of your document? It's easy to do if you consistently use ...

Discover More

Setting the Distance between Text and Borders

Add a border around a piece of text (such as a paragraph), and Word makes some assumptions about the placement of that ...

Discover More

Converting Footnotes to Endnotes

When you spend a lot of time creating footnotes, how can you convert all of them to endnotes without entering them all ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Previous Column Values in a Drop-Down List

Want a quick way to add non-unique values into a column? You probably know you can do this by starting to simply type in ...

Discover More

Deleting Stubborn Links

Deleting unwanted links in a workbook can be a challenge, particularly if you are not sure how those links got there. ...

Discover More

Limiting the Scope of the Undo Command

As you make edits in Excel, the program remembers your actions so that you can later undo them. If you have multiple ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 0?

2023-04-26 11:08:20

J. Woolley

Four things to know about Range.SpecialCells(Args):
1. An error will occur if there is no cell that satisfies Args; the best way to handle this is with On Error..., not If IsError(...).
2. If Range is a single cell, the result is as if Range is ALL cells (Worksheet.Cells); the best way to handle this possibilty is with Application.Intersect. For example,
    Set R = Application.Intersect(Selection, Selection.SpecialCells(...))
3. Range.SpecialCells(Args) fails silently if the result is more than 8,192 NON-contiguous cells, which is probably unlikely.
4. Range.SpecialCells(Args) produces unreliable results when applied directly or indirectly in a user-defined function (UDF); avoid its use in a UDF.

And here is a bonus tip:
5. Worksheet.UsedRange produces unreliable results when applied directly or indirectly in a UDF; avoid its use in a UDF.


2023-04-22 11:06:47

J. Woolley

Adding to Willy's excellent comment, the CleanText2 macro only considers text constants. It does not modify formulas that return blank text or return a result formatted to produce a blank cell. This is appropriate.
However, both versions of CleanText2 fail if there are no text constants, which is probably unlikely. That problem can be avoided by this version:

Sub CleanSheet2()
Dim cell As Range
On Error GoTo Done
For Each cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
If Trim(cell) = "" Then cell.ClearContents
Next
Done: End Sub


2023-04-21 14:32:41

Willy Vanhaelen

The macro CleanSheet2 does a fine job and is very fast too but the layout of the code is a little bit untidy. So I decided to unravel it a bit:
- The 2 last code lines (Set rText = Nothing and Set rCell = Nothing) are not really necessary because the next code line is ''End Sub' which clears all the memory the macro used.
- 'Cells.SpecialCells(xlCellTypeConstants,xlTextValues)' can be used directly in the 'group' argument of the 'For Each ... Next' loop instead of first setting it to the 'rText' variable and then place this variable as 'group' argument.
Here is the result:

Sub CleanSheet2()
Dim cell As Range
For Each cell In Cells.SpecialCells(xlCellTypeConstants,xlTextValues)
If Trim(cell) = "" Then cell.ClearContents
Next
End Sub

This macro does exactly the same as the one of this tip but reordering it made it compacter and easier to understand: if the cell contains only spaces, clear it.
The code is almost self explanatory. For clarity I declared 'cell' as a variable because cell is not a reserved name in VBA.

In fact you can use this macro on almost any worksheet to ensures they don't have any (hidden) cells containing only spaces.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.