|
|
|
|
|
|
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()
Dim cl As Variant
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:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
Test the code:
|
- Enter some spaces (only) in cell A1.
- Highlight cell A1.
- Press Alt + F8 to display the Macro dialog box.
- Choose TrimXcessSpaces and click Okay.
- Verify that the cells are now blank. (Press F2 and try backspacing, or use =len(A1) in cell B2, for example.)
- 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.
|
|