Imported lists often end with stray punctuation. TRIM removes extra spaces, then FIND checks whether the last character is a comma, period or semicolon, and LEFT drops it.
"Acme Plumbing," becomes "Acme Plumbing".
The example
Three values: one with a comma, one with a period and extra spaces, one already clean.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Case | Text | Cleaned | Length |
| 2 | Row 1 | Acme Plumbing, | Acme Plumbing | 13 |
| 3 | Row 2 | Smith and Sons. | Smith and Sons | 14 |
| 4 | Row 3 | Delta Air | Delta Air | 9 |
The formula
The last character is looked up in a short list of punctuation marks:
How it works
Here is the logic, one piece at a time:
TRIM(B2)removes extra spaces.RIGHT(...,1)grabs the last character.FINDchecks if it is in ",.;" andISNUMBERturns that into TRUE or FALSE.LEFT(...,LEN-1)removes it; otherwise the trimmed text is returned.
Run the cleaned column through Paste Special Values before deleting the original.
Try it: interactive demo
Type a value with trailing punctuation.
Variations
More characters
Add them to the list.
Remove all
Loop with SUBSTITUTE.
Pitfalls & errors
An empty cell makes FIND return 1 and LEFT error; wrap in IF(B2="","",...).
Only one trailing character is removed.
Keep the original column until checked.
Practice workbook
Frequently asked questions
Why FIND and not SEARCH?
Several trailing marks?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class