Deleting Leading/Trailing Spaces

[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.

 

[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

 

[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]
-------------------------

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.