How about a macro? Just change the two Const (constant) assignments to the
column letter and sheet name where your data is located at...

Sub CleanUpCommaSpaces()
Dim X As Long
Dim R As Range
Dim LastRow As Long
Const ColumnLetter As String = "A"
Const SheetName As String = "Sheet6"
With Worksheets(SheetName)
LastRow = .Columns(ColumnLetter).SpecialCells(xlCellTypeLast Cell).Row
For X = 1 To LastRow
With .Cells(X, ColumnLetter)
If .Value Like ", *" Then
.Value = Mid(.Value, 3)
ElseIf .Value Like "*, " Then
.Value = Left(.Value, Len(.Value) - 2)
End If
End With
Next
End With
End Sub

--
Rick (MVP - Excel)


"StargateFan" wrote in message
...
On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone"
wrote:


With the list starting in B5...
Fill column C with trimmed data (remove leading/trailing spaces)

Is there a way to automate removing leading/trailing spaces? That
would be so cool. The fields have more than one word in them so that
I can't just do a S&R for spaces.

I'm guessing that the leading/trailing spaces are why this doesn't
work 100% ... (?)

Use this formula in D5 _
and fill down... =IF( LEFT(C5,1) = ",",TRIM(MID(C5,2,255)),C5)

Use this formula in E5 _
and fill down... =IF( RIGHT(D5,1) = ",",TRIM(LEFT(D5,LEN(D5)-1)),D5)
Copy and paste values.
--
Jim Cone
Portland, Oregon USA
(You don't accept Spam, I hope you don't send any)
<lol I sure as heck don't! Never have, in all my years, though it's
just an easy way to put an email address vs. a munged one. <g

"StargateFan"

wrote in message
I was wondering how to clean up a couple of columns. I have
concatenated two fields together that had blank cells in some places.
The results I got after copy-pasting the concatenates formulas into
values show up in these three ways, the first one being okay, the
other two not:
Address1, Address2
(okay)

, Address2
(not okay, need to get rid of comma and space _before_ text)

Address1,
(also not okay, need to get rid of comma and space _after_ the text).

Is there an easier, quicker way to clean up the entries of these
commas and spaces where they're not needed vs. doing the clean up
manually?
Thanks! :oD