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

With the list starting in B5...
Fill column C with trimmed data (remove leading/trailing spaces)
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)



"StargateFan"
<IDon'(E-Mail Removed)>
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! D

On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone"
<(E-Mail Removed)> 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"
><IDon'(E-Mail Removed)>
>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! D

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(xlCellTypeLastCell).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" <IDon'(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone"
> <(E-Mail Removed)> 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"
>><IDon'(E-Mail Removed)>
>>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! D
>

On Tue, 2 Jun 2009 03:54:57 -0400, "Rick Rothstein"
<(E-Mail Removed)> 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...

Macro sounds great, as long as it knows only to delete the values
where there is nothing before the ", " or after it, as sometimes there
are 2 words that need separating.

Thanks, will give this a try at work later this morning!

D

>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(xlCellTypeLastCell).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" <IDon'(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone"
>> <(E-Mail Removed)> 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"
>>><IDon'(E-Mail Removed)>
>>>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! D
>>

On Tue, 2 Jun 2009 03:54:57 -0400, "Rick Rothstein"
<(E-Mail Removed)> 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(xlCellTypeLastCell).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! D

>--
>Rick (MVP - Excel)
>
>
>"StargateFan" <IDon'(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone"
>> <(E-Mail Removed)> 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"
>>><IDon'(E-Mail Removed)>
>>>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! D
>>

>>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(xlCellTypeLastCell).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.

Okay, this macro will process each sheet in the workbook and, yes, it only
does one column (the column letter that you assign to the ColumnLetter
constant... it does this same column on each sheet). And to clarify, it only
removes the comma-space (both characters must be present) from the beginning
or the end of the text in a cell and no where else... and to further
clarify, it only does this for cells contain fixed text (that is, cells
whose value does NOT come from a formula)...

Sub CleanUpCommaSpaces()
Dim X As Long
Dim R As Range
Dim LastRow As Long
Dim WS As Worksheet
Const ColumnLetter As String = "A"
On Error GoTo Whoops
Application.ScreenUpdating = False
For Each WS In Worksheets
LastRow = WS.Columns(ColumnLetter).SpecialCells(xlCellTypeLastCell).Row
For X = 1 To LastRow
With WS.Cells(X, ColumnLetter)
If Not .HasFormula Then
If .Value Like ", *" Then
.Value = Mid(.Value, 3)
ElseIf .Value Like "*, " Then
.Value = Left(.Value, Len(.Value) - 2)
End If
End If
End With
Next
Next
Whoops:
Application.ScreenUpdating = True
End Sub

--
Rick (MVP - Excel)

Concatenate the ranges using this UDF which ignores blank cells so you don't
get the extra spaces and commas when data is missing.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ", "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function


Gord Dibben MS Excel MVP

On Mon, 01 Jun 2009 20:08:35 -0500, StargateFan
<IDon'(E-Mail Removed)> wrote:

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

I thought you would know how to do this...
In C5 enter the formula =TRIM(B5) and fill down
--
Jim Cone
Portland, Oregon USA



"StargateFan"
<IDon'(E-Mail Removed)>
wrote in message
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% ... (?)

I know he asked about leading/trailing spaces, but his original question
showed there were commas in front of the spaces that also had to be removed.
In addition, your suggestion would change the text he wants to preserve if
it happens to have multiple internal spaces (for formatting or column
alignment as an example) since TRIM collapses multiple spaces down to single
spaces.

--
Rick (MVP - Excel)


"Jim Cone" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>I thought you would know how to do this...
> In C5 enter the formula =TRIM(B5) and fill down
> --
> Jim Cone
> Portland, Oregon USA
>
>
>
> "StargateFan"
> <IDon'(E-Mail Removed)>
> wrote in message
> 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% ... (?)
>