How to delete commas and spaces when not needed ... S&R way, or macro way?

[StargateFan]

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

 

[Jim Cone]

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

 

[StargateFan]

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.

<lol> I sure as heck don't! Never have, in all my years, though it's

 

[Rick Rothstein]

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

 

[StargateFan]

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

 

[StargateFan]

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

How about a macro? Just change the two Const (constant) assignments to the

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

 

[Gord Dibben]

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

 

[Jim Cone]

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 address 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% ... (?)

 

[Rick Rothstein]

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.

 

[Jim Cone]

Rick,
I think you missed my original post
Jim Cone


"Rick Rothstein"
<[email protected]>
wrote in message
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 Rothstein]

Yes, I did... sorry.