WebCitz Blog

Excel Tip / Trick: How to Check for and Remove a Trailing Comma

Excel Tip How to Check for and Remove a Trailing Comma

If you have ever worked with a spreadsheet that had lots of data, perhaps thousands or tens of thousands of rows, then you know the importance of formulas. We would like to share a handy formula with you that will check a cell for a trailing comma (or other character) and remove it.

Please keep in mind that this formula actually checks to see if the last character is a comma. It will not just remove the last character in the cell, it will only remove a comma at the end.

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

In the above formula, you will notice that cell A1 is being checked to see if there is a comma at the last position. If a comma is found then it will remove the comma, but if another character is found it will leave it alone. To change it from a comma to another character, such as hyphen, simply change the formula to look like the following:

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

We hope this comes in handy for you! Please remember to always make backups of your files and data before making any changes!

Related Post: What is website maintenance?

Related: Learn this easy trick to remove leading and trailing spaces from cells!

Disclaimer: WebCitz, LLC does not warrant or make any representations concerning the accuracy, likely results, or reliability of the information found on this page or on any web sites linked to from this page. This blog article was written by David W in his or her personal capacity. The opinion(s) expressed in this article are the author's own and may not reflect the opinion(s) of WebCitz, LLC.