How to remove last/first character if it is a comma or certain character in Excel?
When working with data in Excel, it's common to encounter entries where the first or last character is a comma or another unwanted symbol. These situations often arise when exporting data from other systems, merging data sets, or after bulk editing textual information. Removing these stray characters can not only improve the appearance of your data, but also help prevent errors in subsequent analysis and operations, such as searching, matching, or importing to other systems.
This tutorial will guide you through several practical methods to quickly remove the last or first character—if it matches a specific symbol (such as a comma)—from multiple cells in Excel. You will find step-by-step instructions for both Excel formulas and Kutools for Excel utilities, along with additional scenarios and troubleshooting advice to help you choose the most suitable solution for your tasks.
➤ Delete last/first character if it is a comma or certain character with Kutools for Excel
➤ Delete last/first character with VBA code
Delete last/first character if it is a comma or certain character with formula
Excel formulas offer a straightforward way to remove an unwanted first or last character only if it matches a specific symbol from your cell values. This approach is especially useful when you want a dynamic solution and prefer not to install additional add-ins. You can use these formulas for data cleaning tasks on both small and large data sets, and they can be easily extended for use with different symbols by adjusting the parameters.
Delete the last character if it is a comma
To remove the last character from a cell only if it is a comma, follow these steps:
1. Choose a blank cell for your result—for example, D2. Enter the following formula into the selected cell and press Enter to confirm:
=IF(RIGHT(B2,1)=",",LEFT(B2,LEN(B2)-1),B2)
2. Drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to other cells in the list. Each cell will display the text with the last comma removed, only if the last character actually is a comma.
Delete the first character if it is a comma
To remove the first character if it is a comma, use the following method:
1. Select a blank cell (for instance, D2 if your data starts in B2) and enter this formula:
=IF(LEFT(B2,1)=",",RIGHT(B2,LEN(B2)-1),B2)
2. After pressing Enter, copy the formula down as needed to cover your data range. The formula will return the cell value without the leading comma if it finds one.
Note: In both formulas, "B2" refers to the cell you wish to inspect and process, and "," is the symbol to be removed. If you need to target another symbol, such as a semicolon or a dash, replace the comma in the formula accordingly. If your data starts in another column or row, adjust the cell references to match.
Practical tips: These formula-based solutions are best suited for situations where you need to keep the original data intact and output the cleaned data to a separate column. If you want to remove other characters, simply change the character within the quotes in your formula. Remember to check for data inconsistencies before applying the formulas to a large data set, as formulas will only remove the specified symbol and will not affect other unwanted characters.
Error reminder: If your cells are empty or contain only one character (for example, a single comma), the formula may return an empty string. This is expected, but you should be aware when reviewing your results.
Delete last/first character if it is a comma or certain character with Kutools for Excel
If you are working with large data sets and want to simplify the process of detecting and removing unwanted first or last characters, Kutools for Excel provides practical utilities that can help you accomplish this task efficiently. Using Kutools' Select Specific Cells and Remove by Position features allows you to identify and clean up your data without building complex formulas or manually searching for issues. This is particularly useful when you need to clean a wide range of cells in-place, and is ideal for users with intermediate Excel skills or those seeking time-saving automation.
1. Select all cells in your target range that may begin or end with the character you want to assess. Then, click Kutools > Select > Select Specific Cells from the Kutools menu.
2. In the Select Specific Cells dialog box, configure the options as follows for precise selection:
3. With the appropriate cells selected, click Kutools > Text > Remove by Position to open the removal utility.
4. In the Remove by Position dialog box, configure the settings to remove only the specific character. Type 1 in the Numbers box to indicate only the first or last character should be removed. Choose From right to remove the last character, or From left to remove the first character. Make your selection, then click OK to apply the changes.
Now, the unwanted character at the beginning or end of your selected cells will be removed instantly.
Tip: This operation will directly modify your original data, so if you'd like to keep a copy of the initial content, consider duplicating your column before starting. Kutools' approach is especially helpful if your selection includes hidden rows or filterable results, as the tools work reliably on visible cells.
Precaution: Double-check that you have correctly selected all target cells prior to using the Remove by Position tool, to avoid accidental data modifications.
If you encounter issues where not all characters are removed as expected, ensure your selection matches the character you input. Also, confirm that there are no leading/trailing spaces, which may affect detection accuracy.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Delete last/first character with VBA code
For users seeking to automate this cleaning task across a variable range, using VBA (Visual Basic for Applications) code can provide a flexible solution. The VBA method is suitable when you need to process multiple sheets, large data sets, or prefer a reusable macro for future use. VBA code enables you to remove the first or last character only if it matches a specified symbol such as a comma, semicolon, or other.
1. To begin, click Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window that appears, go to Insert > Module.
2. Copy and paste the following VBA code into the Module:
Sub RemoveFirstOrLastIfChar()
Dim WorkRng As Range
Dim Rng As Range
Dim xTitleId As String
Dim RemoveChar As String
Dim PositionType As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select Range to clean", xTitleId, WorkRng.Address, Type:=8)
RemoveChar = Application.InputBox("Character to remove (e.g. , or ;)", xTitleId, ",", Type:=2)
PositionType = Application.InputBox("Remove from (Enter 'Left' or 'Right')", xTitleId, "Right", Type:=2)
Application.ScreenUpdating = False
For Each Rng In WorkRng
If PositionType = "Right" Then
If Right(Rng.Value, 1) = RemoveChar Then
Rng.Value = Left(Rng.Value, Len(Rng.Value) - 1)
End If
ElseIf PositionType = "Left" Then
If Left(Rng.Value, 1) = RemoveChar Then
Rng.Value = Right(Rng.Value, Len(Rng.Value) - 1)
End If
End If
Next
Application.ScreenUpdating = True
End Sub3. To run the code, click the 
button in the VBA editor. When you execute the macro, Excel will prompt you to select the range to clean, input the target character (such as a comma), and specify from which side ("Left" or "Right") to remove it.
The code will quickly review each cell in your chosen range and remove the first or last character where it matches your input. The macro is designed to skip cells that do not have the specified symbol at the chosen position, helping ensure you do not lose unintended data.
Practical tip: You may want to save your workbook before running the macro, especially if applying changes to a large data range or important data. Macros can be customized to operate on specific sheets, or enhanced to process multiple characters if needed.
Error reminder: Always ensure you enter the character exactly as it appears in your data, and carefully select "Left" or "Right" during processing. Inputs are case-sensitive and must match cell contents exactly.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in