Remove Last Character in Excel

Most of my work involves getting data from various sources, such as databases, webpages, or other people.

In most cases, the first step is to clean the data and make it usable for my analysis.

Sometimes, I get datasets that have an additional character in the end that I want to remove. Thanks to some amazing features and formulas in Excel, this is a cakewalk.

In this article, I will show you some easy ways to remove the last character in a cell in Excel.

Using the LEFT Formula to Remove the Last Character

Let’s start with an easy formula.

Below, I have a dataset where I have some product IDs in column A, and I want to remove the last character, which is a number.

To do this, I can use the below formula in column B:

=LEFT(A2,LEN(A2)-1)

I entered this formula and cell B2 and then copied it for all the other cells in the column.

The LEFT function allows us to extract a specific number of characters from the left.

It takes two arguments:

• Text – This is the first argument that refers to the cell from which we want to extract the characters from the left. In our example, I’ve given A2 as the cell reference.
• Num_Chars – this is the number of characters I want to extract from the left from the given cell reference. In our example, since the overall length of the string in each cell varies, I have used the LEN function first to calculate the length of the string in each cell and then subtract 1 from it to give us the number of characters that should be extracted from the left.

Once you have the result, you can remove the formula and keep the data (by copying the cells and pasting it over itself as values)

Also read: Remove Characters From Left in Excel

Flash Fill To Remove Last Character in Excel

Another quick and easy way to do this is by using the Flash Fill feature in Excel.

To use Flash Fill, you need to give it one or two instances of the result that you want. It then analyzes these results, identifies the pattern, and then repeats the same to give you the result for all the other cells in the column.

Let me show you how it works.

Below, I have a dataset where I have some product IDs in column A, and I want to remove the last character, which is a number.

Here are the steps to do this using Flash Fill:

1. In cell B2, enter the result you want. In this example, I will manually enter AUT.

2. Hit the Enter key. This will bring the cursor to the cell below.
3. Hold the Control key and press the E key. This is the keyboard shortcut to run Flash Fill. You can also run Flash Fill by going to the Home tab, then clicking on the Fill icon, and then clicking on the Flash Fill option.

As soon as you run Flash Fill, it tries to identify the pattern based on the manual entry unit in cell B2 and gives you the result based on this pattern in all the other cells in the column.

While Flash Fill Worked as expected in our case, you should always double-check the results of Flash Fill as it can sometimes get the pattern wrong. If that happens, you can manually enter the results in two or three cells and then use Flash Fill so it would have more results to get the pattern right.

Also read: How To Remove Text Before Or After a Specific Character In Excel

VBA to Remove Last Character

If your work involves removing the last character from a range of cells frequently, you can also consider using a VBA code to do this.

While setting up the VBA code may involve a couple of additional steps, once you are done setting it up, you can reuse the VBA code again and again in the file in which it is stored.

Below is the VBA code that would remove the last character from all the cells in the selected range

Sub RemoveLastCharacter()

    Dim rng As Range
    Dim cell As Range

    Set rng = Selection

    For Each cell In rng
        If Len(cell.Value) > 0 Then
            cell.Value = Left(cell.Value, Len(cell.Value) - 1)
        End If
    Next cell

End Sub

This above VBA code loops through each cell in the selected range and uses the LEFT function to return the string without the last character.

Here are the steps to put the VBA code in Excel:

1. Hold the Alt key and then press the F11 key. This will open the VB editor for your Excel workbook. If you’re using a Mac, you can use Opt + F11.
2. In the VB Editor, click on the Insert option and then click on Module. This will insert a new module for the workbook.

3. Copy and paste the above VBA macro code into the module code window.

4. Click on the Save button and close the VB Editor. It may ask you to save the file as a .XLSM (macro-enabled) file (as you can not store macros in an XLSX file)

The above steps would put the VBA code in your workbook, and now you can use the steps below to run the macro code:

1. Select the cells from which you want to remove the last character.
2. Click on the Developer tab and then click on the Macros icon. This will open the Macro dialog box.

3. Select the Macro from the list (our macro is called RemoveLastCharacter)

4. Click on the Run button.

As soon as you click on the run button, it will execute the macro in the back end, and the last character from each cell in the selected range will be removed.

Keep in mind that the result you get after running a VBA code is irreversible, and the changes cannot be undone. So I strongly recommend you create a backup copy of your dataset before running any VB macro script.

If you want to reuse this VB macro code in all the workbooks on your system, you can save this in your Personal macro workbook (follow the steps shown here to activate and use the Personal Macro workbook)

So these are three simple ways you can use to quickly remove the last character from a cell or selected range of cells in Excel.

I hope you found this article helpful. If you know of any other method to remove the last character or if you have any feedback or suggestions for me, do let me know in the comments section.

Other Excel articles you may also like:

• Remove the First Character from a String in Excel
• Extract the First Word from a Text String in Excel
• Extract a Substring in Excel (Using TEXT Formulas)
• Find Position of the Last Occurrence of a Character in a String in Excel