Remove Last Comma

[pinkgemini]

Hi There,
I'm doing data clean up for a client & have run across data that contains a comma at the end. They've supplied it to me this way & from what I can see it just ends (there is no space after the final comma) IE:

Cheerful, Chipper, Convivial, Delighted, Ecstatic, Elated, Exultant, Pleasant, Pleased, Sparkling, Sunny, Tranquil, Unadorned, Symphony,

The next row may be similar in descriptors but not contain the extraneous comma at the end. IE:

Compassionate, Tender, Calm, Countrified, Priestly, Tranquil, Unadorned, Symphony, Pastoral

Is there a way to remove the final comma in the fields where it's just "dangling" at the end but not remove it from the fields that don't contain the extraneous comma?

Obviously Find & Replace doesn't work because I need the commas to remain throughout the rest of the data. I've searched the boards & have not found a formula that works...if I've missed something that's already posted I apologize. I'm going a bit cross eyed at the moment & with thousands of rows to go through, I'd hate to have to remove the ending comma's one at a time!

Many thanks,
pinkgemini

 

[barry houdini]

Hello pinkgemini welcome to MrExcel. If data is in A1 try this formula

=LEFT(A1,LEN(A1)-(RIGHT(A1)=","))

 

[pinkgemini]

Barry Houdini, you saved my week (and my vision)!

Thank you so very much for your very quick & perfect reply...you're the best!

Many, many thanks,
pinkgemini

 

[pinkgemini]

Sorry to be a pest...Barry Houdini's formula was perfect for most of the data I'm working on, but I've encountered a few times where there's a space after the extraneous comma so the Formula does not work in those fields.

I thought (wrongly) that I could just adjust the formula with a comma & a space (have tried adding it in the formula several ways(comma space in quotes) ", " and (space comma in quotes) " ," and (comma in quotes & a space in quotes)","" ". Obviously I'm no Excel guru so any advice would be greatly appreciated!

Best Regards,
pinkgemini

 

[Rastaman]

How about this:

=LEFT(A1,LEN(TRIM(A1))-(RIGHT(TRIM(A1))=","))

Rick

 

[pinkgemini]

Thank you Rastaman, that worked perfectly!j

So glad to have found the Mr.Excel forum...you guys all rock!

Many thanks

 

[excelmelbz]

Hello pinkgemini welcome to MrExcel. If data is in A1 try this formula

=LEFT(A1,LEN(A1)-(RIGHT(A1)=","))

Thanks Barry it worked for me perfectly!

 

[THAuzhavan]

Rastaman(Jul 15th, 2008, 08:51 AM) answer helped me lot in the upload of ta.wiktionary. Thanks Indeed. Is it possible to replace instead of removal of the last character. I want to replace last semicolon with |(pipeline) instead of remove. This is for my 30,000 word list.

 

[Rastaman]

Do you always want the pipeline character at the end of every string, or are there times when you don't want it? If you always want it, can you concatenate onto the existing formula?

=LEFT(A2,LEN(TRIM(A2))-(RIGHT(TRIM(A2))=","))&"|"

If you only want it when the string ends in a comma:

=LEFT(A4,LEN(TRIM(A4))-(RIGHT(TRIM(A4))=","))&IF(RIGHT(TRIM(A4))=",","|","")


Rick

 

[gorem]

I may have misinterpreted your question, but have you looked at =SUBSTITUTE ? SUBSTITUTE function - Excel