Depending on what your data looks like, maybe you could change the leading
spaces (or all the spaces) to an unique unused character--I sometimes use a
dollar sign--but I have to be careful that it's not used elsewhere--and I don't
have any cells that would look like money when I'm doing this.
Then do the data|text to columns,
then edit|replace
what: $
with: (spacebar)
if you end up with trailing spaces you want to get rid of, you can run a macro
after the data|text to columns.
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, ActiveSheet.UsedRange, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "Please select a good range!"
Exit Sub
End If
If myRng.Cells.Count = 1 Then
MsgBox "just one cell????"
Exit Sub
End If
myRng.Replace what:="$", replacement:=" ", lookat:=xlPart, MatchCase:=False
For Each myCell In myRng.Cells
If Trim(myCell) = "" Then
myCell.ClearContents
Else
myCell.Value = RTrim(myCell.Value)
End If
Next myCell
End Sub
And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
(If the cell is filled with space characters, it gets cleared, otherwise, it
just trims the blanks from the right hand side.)
