Deleting Leading/Trailing Spaces

S

So_Long

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I have a spreadsheet that has a lot of leading and trailing spaces. For example, consider the following cell values...

Arizona
[SPACE]Arizona
Arizona[SPACE]

In the second example, there's a space before the word Arizona. In the third example, there's a trailing space - a space after Arizona.

Is there some way to purge all the leading and trailing spaces from a spreadsheet?

Thanks.
 
M

macropod

Hi So Long,

Here's a 'TrimRange' macro that should work in all Excel versions except 2008 (which doesn't support vba):

Option Explicit
Dim SBar As Boolean

Private Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

Sub TrimRange()
Call MacroEntry
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
Dim I As Long
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Value = Application.Trim(Cell.Value)
I = I + 1
Application.StatusBar = Int(I / CellCount * 100 + 0.5) & "% Trimmed"
Next Cell
MsgBox "Finished trimming " & CellCount & " cells.", 64
Call MacroExit
End Sub

Cheers
 
M

macropod

Hi So Long,

Yeah, I know - I should have read you post more closely - you're using Excel 2008. In that case, you could try:
=TRIM(A1)
where the text you want to trim is in A1.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

macropod said:
Hi So Long,

Here's a 'TrimRange' macro that should work in all Excel versions except 2008 (which doesn't support vba):

Option Explicit
Dim SBar As Boolean

Private Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

Sub TrimRange()
Call MacroEntry
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
Dim I As Long
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Value = Application.Trim(Cell.Value)
I = I + 1
Application.StatusBar = Int(I / CellCount * 100 + 0.5) & "% Trimmed"
Next Cell
MsgBox "Finished trimming " & CellCount & " cells.", 64
Call MacroExit
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I have a spreadsheet that has a lot of leading and trailing spaces. For example, consider the following cell values...

Arizona
[SPACE]Arizona
Arizona[SPACE]

In the second example, there's a space before the word Arizona. In the third example, there's a trailing space - a space after
Arizona.

Is there some way to purge all the leading and trailing spaces from a spreadsheet?

Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top