Get rid of ALL trailing commas in a text string

[diannao man]

Hi all

I'm using Excel 2003, Windows XP

I'm trying to get rid of all trailing commas in a text string, for example:

"Steve Smith,,,,,,,,,,,"
"Sarah Golding,,,,,,"

The number of trailing commas varies in a list of 500 names - I've tried a few formulas including these two:

=LEFT(A1,LEN(A1)-1)&SUBSTITUTE(RIGHT(A1), ",", "")
=IF(RIGHT(TRIM(A1),1)=",",LEFT(TRIM(A1),LEN(TRIM(A1))-1),A1)

But these only delete the last comma, where as I'm trying to delete all trailing commas. I've searched these boards somewhat, but can only find formulas similar to the ones above. Can anyone help?

Thanks very much

Diannao man

 

[SteveO59L]

If there are no other comma's, try

Find/Replace

 

[Gerald Higgins]

Or

=substitute(A1,",","")

but again this only works if there are no other commas.

 

[diannao man]

Hi Steve, thanks for your quick reply. Unfortunately I can't use find/replace as there are other commas which need to be preserved - most of the names are as the examples I posted, but there are quite a few like:

Karen Jones, Jim Bob,,,,,,,,,,,

I only need to get rid of the trailing commas in cases like that

thanks again

 

[Gerald Higgins]

OK, so how do you identify which are trailing commas, and which need to be kept ?

 

[diannao man]

Hi Gerald, basically only the commas at the end of the text string so in:

Karen Jones, Jim Bob, Sarah Golding,,,,,,,,,,

The commas after "Golding" all need to be deleted, whereas the others need to be kept in - is that even possible!!?

 

[mikerickson]

For a one time routine, you could put this in B1 (dragged down)
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

and drag that right until they are all gone.

For regular use, you could use this CSE formula

=REPLACE(A1,MATCH(1, IF(MID(A1&REPT(",",26), COLUMN(A1:Z1), 1)<>",",1))+1, 255, "")

entered with Ctl-Shift-Enter (Cmd+Return for Mac)

 

[Gary McMaster]

Replace 2 commas with one comma. Keep repeating "Replace All" until all strings have a single trailing comma and then use your original formula.

Gary

 

[doofusboy]

This seems to do the trick:

=LEFT(A1,LEN(A1)-(LEN(A1)-SEARCH(",,",A1)+1))

 

[Rick Rothstein]

If you are trying to "purify" your actual data, in place, the you will need a VBA macro to do it. Select the cells containing your data (it is alright if cells without trailing commas are selected as they will not be changed) and then run either one of these macros... your choice as they both do the same thing, but use different techniques to accomplish their goal...

Sub RemoveTrailingCommas_Version1()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = Replace(Replace(RTrim(Replace(Replace(Cell.Value, " ", Chr(1)), ",", " ")), " ", ","), Chr(1), " ")
  Next
End Sub
 
Sub RemoveTrailingCommas_Version2()
  Dim X As Long, Cell As Range, CellVal As String
  For Each Cell In Selection
    CellVal = Cell.Value
    For X = Len(CellVal) To 1 Step -1
      If Mid(CellVal, X, 1) <> "," Then
        Cell.Value = Left(CellVal, X)
        Exit For
      End If
    Next
  Next
End Sub