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

 

[Gerald Higgins]

IMHO, VBa is not required, and doofusboy's suggestion neatly does it.

 

[diannao man]

Wow doofusboy, your formula worked absolutely spot on, a massive thank you. Thanks to everyone else for your help on this - I tried the array formula, but that seemed to delete everything after the first column? I will try the VBA method too, just to give it a go.

Anyway, thanks again everyone for helping me resolve it so quickly

Diannao Man

 

[Rick Rothstein]

I will try the VBA method too, just to give it a go.

Just remember, the VBA method is set up to physically change your original data within their own cells although I can change it to output the modified data to different cells in order to match how the formula solution works.

 

[Rick Rothstein]

This seems to do the trick:

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

Of course, this formula assumes there will be no double commas (or more) located within the text itself, for example, like this...

one,two,,four,five,,,,,

 

[pgc01]

This seems to do the trick:

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

Hi doofusboy

Your formula can be simplified to :

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

In addition to Rick's remark, it doesn't work if the string end with just 1 comma, maybe you can tweak it?

 

[shg]

of course, this formula assumes there will be no double commas (or more) located within the text itself, for example, like this...

One,two,,four,five,,,,, <!-- / message -->

=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

 

[Rick Rothstein]

=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

Of course this formula assumes there will not be multiple continguous blank spaces within the the text (as the TRIM function will collapse them). I had thought of this formula (it is kind of what my Version1 macro does except VB has a RTrim function that Excel doesn't), as well as the one doofus posted, but opted for the VBA solution because it handles all possible situation. As with any text parsing formula/code, it is always useful to know the ways in which the data being parsed can be constructed. If the OP does not tell us, then we are left guessing.

 

[Marcelo Branco]

Maybe...

=LEFT(SUBSTITUTE(A1,",,",","),SEARCH(",,",SUBSTITUTE(A1,",,",",")&",,")-1)

M.

 

[Rick Rothstein]

Maybe...

=LEFT(SUBSTITUTE(A1,",,",","),SEARCH(",,",SUBSTITUTE(A1,",,",",")&",,")-1)

Double internal commas, if any, get collapsed to single commas and if you have three internal commas in a row, the text gets chopped off at that point.

 

[doofusboy]

This works whether there are zero, one, or many commas at the end of your string:

=IF(RIGHT(A4,1)<>",",A4,IF(AND(MID(A4,LEN(A4),1)=",",MID(A4,LEN(A4)-1,2)=",,"),LEFT(A4,LEN(A4)-(LEN(A4)-SEARCH(",,",A4)+1)),IF(AND(MID(A4,LEN(A4),1)=",",MID(A4,LEN(A4)-1,2)<>",,"),LEFT(A4,LEN(A4)-1))))

Will have to think about double-comma in middle of string if you think that's a possibility for your data.