We Value Your Privacy

We and our partners use technology such as cookies on our site to personalise content and ads, provide social media features, and analyse our traffic. Click below to consent to the use of this technology across the web. You can change your mind and change your consent choices at anytime by returning to this site.

Update Consent
Loading ...

How to Remove Trailing Commas in Excel

Updated February 21, 2017

Sometimes you may find unpleasant surprises in data when you import a file into the Microsoft Excel spreadsheet application. In some cases, you might have an extraneous character, such as a comma, that appears at the end of cell strings. To remove trailing commas from your Excel cells, you can create a function that checks for a comma at the end of a string and then deletes the final character in the string if it is a comma. Once you've used the formula to strip commas from the cell strings, you can use the Paste Special function to copy the formula output values back to the original data.

Loading ...
  1. Open the worksheet that contains the data from which you want to remove trailing commas.

  2. Right-click the header of the column directly to the right of the data column that you want to clean. Click "Insert" in the menu to insert a new function column.

  3. Type the following in the cell in the formula column adjacent to the first data cell:

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

  5. Substitute the cell address of your first data cell in place of all instances of "A1" in the above example.

  6. Press "Enter." Excel first determines whether the rightmost value in the data cell is a comma. If so, it determines the number of characters in the cell using the "Len" function and then returns only the leftmost N minus 1 characters, thus omitting the comma. If no comma is detected at the end of the string, then Excel returns the original cell value.

  7. Right-click the formula cell and click "Copy." Paste the formula into the cell directly to the right of all cells from which you want to clean the commas. Excel will perform the comma-trimming function on all cells and return the update value in the formula column.

  8. Highlight all formula cells, then right-click the array and choose "Copy."

  9. Highlight the original data cells, then right-click the array and choose "Paste Special." Click the radio button next to "Values," then click the "OK" button. Excel will copy the output strings from the comma-less formula cells into your original data cells as static character strings.

  10. Highlight the formula column, then right-click the array and click "Delete" from the menu. This will delete the formula column now that a permanent copy of the formula output has been saved in the original data column.

Loading ...

About the Author

Dan Howard is a sports and fitness aficionado who holds a master's degree in psychology. Howard's postgraduate research on the brain and learning has appeared in several academic books and peer-reviewed psychology journals.

Loading ...