On Tue, 2 Jun 2009 03:54:57 -0400, "Rick Rothstein"
wrote:

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

Oh, just noticed, will this do the whole workbook, or just sheetname
"Sheet6", since all our sheets have specific labels. But what a good
idea if the macro could handle the entire workbook at once!

Also, does Const ColumnLetter As String = "A" mean that only column A
will be handled? That would be great because I could then choose the
actual real columns to fix each time, in this case, each worksheet has
2 columns.

Thanks! :oD

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