Looking to code a function to efficiently remove multiple commas in a string

[rlv]

Hi Mike. Thanks for taking a crack at it. I think my sample spreadsheet may have mislead you into thinking I wanted to loop through cells D3 to D8. Really, I was just using those cells to store different trial strings to paste into D2. Sorry for the confusion.


Your sub takes a similar approach to the Function CollapseCommas3a I posted above, though it did eat the existing space in "thank you". When I extended it extended to preserve any existing spaces, it worked fine.

Sub def()
    Const cDelimiter As String = ","
    Const cReplaceDelimiter As String = " "
    Dim cell As Range


    For Each cell In Range("d2", Cells(Rows.count, "d").End(xlUp))
        Cells(cell.Row, 2).Value = Replace(Replace(Application.WorksheetFunction.Trim(Replace(Replace(cell.Value, cReplaceDelimiter, Chr(248)), cDelimiter, cReplaceDelimiter)), cReplaceDelimiter, cDelimiter), Chr(248), " ")   'restore the spaces
    Next
End Sub