Excel

Eliminate excess spaces from selection

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Ken Puls

Description:

This code works on the selected cells, and allows you to remove any excess spaces in a string of characters, leaving only a single space between words. If no words exist, it will clear the cell. 

Discussion:

This macro has two benefits, and will be most useful to those who receive Excel files from users who are not knowledgeable in how to use Excel properly. The first benefit is that it will correct irregular spacing. The second use, however, is that it can clear all cells where a user has entered a space to clear the cell instead of deleting the data in it. Clearing a cell using a space can be a very big issue for a VBA programmer as a cell with a space in it is not blank, and many functions/loops test for blank cells as a condition. Running this macro on all cells in the worksheet before testing for blank cells can help ensure that the blank looking cells are actually blank. 

Code:

instructions for use

			

Option Explicit Sub TrimXcessSpaces() 'Macro Purpose: To trim all excess spaces out of cells. This 'eliminates issues where users have cleared the cell with a space, 'and elimates all extra spaces at the beginning or end of a string Dim cl As Variant 'Loop through cells removing excess spaces For Each cl In Selection If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then cl.Value = WorksheetFunction.Trim(cl) End If Next cl End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. Enter some spaces (only) in cell A1.
  2. Highlight cell A1.
  3. Press Alt + F8 to display the Macro dialog box.
  4. Choose TrimXcessSpaces and click Okay.
  5. Verify that the cells are now blank. (Press F2 and try backspacing, or use =len(A1) in cell B2, for example.)
  6. Try it with some text with extra leading/trailing spaces.
 

Sample File:

TrimSpaces.zip 8.79KB 

Approved by mdmackillop


This entry has been viewed 240 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express