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.
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
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.
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:
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.
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!
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 MoreDeleting unwanted links in a workbook can be a challenge, particularly if you are not sure how those links got there. ...
Discover MoreAs you make edits in Excel, the program remembers your actions so that you can later undo them. If you have multiple ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments