Written by Allen Wyatt (last updated December 19, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
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:
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.
Note:
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.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Need to enter information into a bunch of cells that aren't anywhere near each other in the worksheet? Here's a handy way ...
Discover MoreGot some numbers and letters mixed up in the same cell? You may need to get rid of those letters so you are left with ...
Discover MoreThere are a few editing tricks you can apply in Excel the same as you do in Word. Selecting a word from the text in a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments