Excel.Tips.Net Welcome toExcel.Tips.Net

Helpful Links

Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment

Tips.Net Store

ExcelTips FAQ
ExcelTips Premium

Learn Access Now
Free Printable Forms

Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

Newest Tips

Recording a Macro

Adding a Little Animation to Your Life

Converting a Range of URLs to Hyperlinks

Making the Formula Bar Persistent

Engineering Calculations

Digital Signatures for Macros

Fixing the Decimal Point

 

Getting Rid of Spaces in Cells

Summary: Importing data into Excel that was generated in other programs can have some interesting side effects. For instance, you may end up with "blank" cells that actually have spaces in them. To get rid of these extraneous characters, you'll appreciate the techniques described in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

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 versions: 97 | 2000 | 2002 | 2003 | 2007

Save Time and Money! Many people need to keep track of employee time, but don't know where to start when it comes to creating a spreadsheet. Here's a way to save time, effort, and money with ready-to-use timesheet templates.
 
Check out Timesheet Templates today!