Solved: Excel 2003 - Need to clean extra commas from string

[frank_m]

I need to clean the extra comma's out of a string.

Every place you see one or more comma's should be just one with one space after it. I also would like to remove the leading comma

all instances of ", , " and ", , , " should be changed to ", "

example string:
, SampleUser, , 12/14/10 Out, SampleUser, 101, , 12/14/10 In, Needs Calibration, OtherUserSample, , 12/15/10 Out, ok, OtherUserSample, 20, , 12/15/10 In, Needs Calibration, OtherUserSample, 12/16/10 Out, Needs Calibration, OtherUserSample, 132, ok, 12/16/10 In, Needs Calibration, SampleUser, 12/16/10 Out, ok, SampleUser, , , 12/16/10 In, ok

Below is what I've tried so far, but it is not doing anything
Range("A1").Value = Trim(Replace(Replace(Replace(Range("A1").Value, ",", ", ") _
, ", ,", ", "), ", , ,", ", "))
Thanks

[frank_m]

I worked it out like below. - It seems to do the job.
Sub Macro2()

Range("A1").Value = Replace(Replace(Replace(Replace(Range("A1").Value, ", ,", ", ") _
, ", ,", ", "), ", , ,", ", "), " ", " ")

If Left(Range("A1").Value, 1) = "," Then
Range("A1").Value = Trim(Mid(Range("A1").Value, 2))
End If

End Sub

[Bob Phillips]

Is this just a one off, or do you have a column of these to process.

[frank_m]

Just one, the column 12 cell in the selected row.

The code only runs when I click a button named "Finish Row edit"

The code that I put together below seems to work fine, but admittedly there likely is a more eloquent method, plus I haven't tested it extensively yet.
ActiveCell.Offset(0, 12 - ActiveCell.Column).Value = Replace(Replace(Replace(Replace _
(ActiveCell.Offset(0, 12 - ActiveCell.Column).Value, _
", ,", ", "), ", ,", ", "), ", , ,", ", "), " ", " ")
If Left(ActiveCell.Offset(0, 12 - ActiveCell.Column).Value, 1) = "," Then
ActiveCell.Offset(0, 12 - ActiveCell.Column).Value = _
Trim(Format(Mid(ActiveCell.Offset(0, 12 - ActiveCell.Column).Value, 2), "@"))
End If

[macropod]

Hi Frank,

Here's something a bit more flexible:
Sub Demo()
Dim StrTmp As String
StrTmp = Trim(ActiveCell.Value)
While InStr(StrTmp, ", ,") > 0
StrTmp = Replace(StrTmp, ", ,", ",")
Wend
If Left(StrTmp, 1) = "," Then StrTmp = Right(StrTmp, Len(StrTmp) - 1)
If Right(StrTmp, 1) = "," Then StrTmp = Left(StrTmp, Len(StrTmp) - 1)
ActiveCell.Value = Trim(StrTmp)
End Sub
This code allows for any number of 'empty' segments and for commas at either end of the string. If you always have a comma at the start and/or never at the end, the code could be further simplified.

[shrivallabha]

Just curious I tried this with array

Sub CleanUp()
Dim MyArray As Variant
Dim MyString As String
MyArray = Split(Sheet1.Range("A1").Value, ",")
MyString = ""
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i) <> " " Then
If MyArray(i) <> "" Then
MyString = MyString + "," + MyArray(i)
End If
End If
Next
Sheet1.Range("B1").Value = MyString
End Sub


As Paul has marked you may need flexibility since (probably) the exact "," cases won't be easy to surmise.

[frank_m]

Hi shrivallabha,

Your array code really is far better than what I had, as the the many different possible case's that I threw at it all were processed correctly.

Nice work and thank you very much :bow:

In the version of your code below, I adapted it to handle the column 12 cell of the selected row
Hi Dim MyArray As Variant
Dim MyString As String, i As Integer
MyArray = Split(ActiveCell.Offset(0, 12 - ActiveCell.Column), ",")
MyString = ""
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i) <> " " Then
If MyArray(i) <> "" Then
MyString = MyString + "," + MyArray(i)
End If
End If
Next
ActiveCell.Offset(0, 12 - ActiveCell.Column).Value = MyString
If Left(ActiveCell.Offset(0, 12 - ActiveCell.Column).Value, 1) = "," Then
ActiveCell.Offset(0, 12 - ActiveCell.Column).Value = _
Trim(Format(Mid(ActiveCell.Offset(0, 12 - ActiveCell.Column).Value, 2), "@"))
End If Hi macropod,

Thanks for you idea. - In order to try your code I would have to incorporate several more Replace cases, as I had with my version, and a couple other related changes, but it looks like it would work well.

I appreciate your time :)

[macropod]

Hi macropod,

Thanks for you idea. - In order to try your code I would have to incorporate several more Replace cases, as I had with my version, and a couple other related changes, but it looks like it would work well.

I appreciate your time :)Hi frank,
I'm not sure what you mean by 'other related changes', but the code I posted will handle however many empty elements there are in your data. try it & you'll see.

[frank_m]

Hi macropod,

I certainly apologize, as I had made a mistake before when I tested your code. I see now that it does work very nicely with the sample string I provided, and it certainly is better and more flexible than what I had. -

However, shrivallabha's code I do like more because it handles cases that are not demonstrated in the sample string, such as multiple commas without spaces (,,,,,,). As far as I know the data will always have spaces between multiple commas, so your code should be fine, I just prefer having the extra protection.

again, many thanks for your time.