Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Getting Rid of Spaces in Cells.

Getting Rid of Spaces in Cells

by Allen Wyatt
(last updated August 3, 2013)

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. Repeat steps 2 through 5, but this time use only one space in step 2.
  7. 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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2883) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Adjusting Column Width from the Keyboard

It's easy to adjust the width of table columns using the mouse, but what if you don't want to use the mouse? Adjusting column ...

Discover More

Managing Corporate Templates

Templates are used to store styles and lots of other customizations that affect how you use Word. On a single-user machine, ...

Discover More

Changing the Document Window Background Color

Word's default black text and a white background may not appeal to everyone. There are a couple of ways to change the ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

MORE EXCELTIPS (MENU)

Forcing Input to Uppercase

If you type information into a workbook, you may want to make sure that what you type is always stored in uppercase. There is ...

Discover More

Inserting Rows

Need to insert rows in your worksheet? Excel provides a few techniques you can use to do this. Here are some ideas you can ...

Discover More

Changing Multiple Cells at Once

Excel includes several different methods of editing information in your cells. If you want to edit multiple cells all at the ...

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 for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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

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.

Links and Sharing
Share