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

 

[diannao man]

I see no comments about the second formula in post #7, does it do what the OP wants?

Hi Mike, sorry I didn't make it clear - my comment in post 12 was about your second formula, I mentioned that it seemed to delete everything after the second occurrence of a comma. (although I said "column" instead of "comma" in my original reply!!)

thank you anyway

There are also quite a few new formulas posted here, - Rick and pgc01's array formulas seem to do the trick. As for double commas within the text string, I reckon using the array formulas first to clear up the trailing commas, then using a simple substitute formula would do the trick. Thanks for the efforts everyone

 

[magicmiles]

=substitute(substitute(trim(substitute(substitute(trim(a1), " ", char(143)), ",", " ")), " ", ","), char(143), " ")

      ----------------------A---------------------- -----------------B-----------------
  1   one,two,,four,five,,,,,                       one,two,four,five                 
  2   Karen Jones, Jim Bob, Sarah Golding,,,,,,,,,, Karen Jones, Jim Bob, Sarah Golding

Hope this isn't considered creating noise in this thread and detracting from the many solutions provided but just hopping in here to say this worked and saved me an hours-long headache in which I considered starting over a project.

Thanks to all you Excel superheroes (especially @shg in this case) that make us look good to our coworkers that think we are Excel champs (when in reality we are just good at Googling - aka "researching" - and Frankensteining spreadsheets lol)