can't remove commas at the end of a string

[dschmitt]

In an Excel column I have strings of text. Some end with a comma. Others end without a comma. I want to remove all the commas at the end of the strings. How do I do that? Below is an example.

Column A
large building
small building,
skyscraper,
tall, house
house boat
cabin

Note: the comma after "tall" I don't want to remove.

[Andrew-R]

Insert column B and into it put the formula:

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

Copy down, then copy the column, paste special|values over the data in column A and then delete column B

[dschmitt]

very strange. If I type a string with a comma at the end into A1 and use your command in B1 it works. However, if I use the command in my data it doesn't work. What could the problem be?

[Andrew-R]

Could your data have trailing spaces after the comma, perhaps?

[dschmitt]

yes, that was the problem. With TRIM I was able to remove the trailing spaces. Thank you very much for the very speedy help.
Problem solved.

[dschmitt]

Andrew, one more question.
I tried to build your command into a small script, but I failed. What did I do wrong here.

Cells(x, 1).Value = Left(Cells(x, 1), Len(Cells(x, 1)) - (Right(Cells(x, 1), 1) = ",")).Value

[Andrew-R]

You don't need the .Value on the end.

Also, because worksheet functions return a value of 1 for a true statement, while VBA uses a value of -1 you have to change the last minus to a plus:

Please Login or Register  to view this content.

[dschmitt]

I see. I understand. I tested it. It works.
Thank you very much again.