Remove Last 2 Characters In Excel Instantly

Are you tired of manually deleting the last two characters in a cell in Excel? Whether you're working with a large dataset or just need to make a quick edit, there are several ways to remove the last two characters in Excel instantly. In this article, we'll explore the most efficient methods to help you get the job done quickly.

Using Formulas to Remove Last Two Characters

One of the most common ways to remove the last two characters in Excel is by using formulas. Here are a few options:

1. Using the LEFT Function

The LEFT function is a great way to remove the last two characters from a cell. Here's the syntax:

=LEFT(A1, LEN(A1)-2)

Assuming the text you want to edit is in cell A1, this formula will return the text without the last two characters.

2. Using the LEN and RIGHT Functions

Another way to remove the last two characters is by using the LEN and RIGHT functions together. Here's the syntax:

=RIGHT(A1, LEN(A1)-2)

This formula will also return the text without the last two characters.

3. Using the SUBSTITUTE Function

If you want to remove a specific character or set of characters from the end of a cell, you can use the SUBSTITUTE function. Here's the syntax:

=SUBSTITUTE(A1, "ab", "")

Assuming the last two characters you want to remove are "ab", this formula will replace them with nothing, effectively removing them.

Using VBA Macros to Remove Last Two Characters

If you need to remove the last two characters from a large dataset or want to automate the process, you can use a VBA macro. Here's an example code:

Sub RemoveLastTwoCharacters()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = Left(cell.Value, Len(cell.Value) - 2)
    Next cell
End Sub

To use this macro, simply select the cells you want to edit, go to the Developer tab in Excel, click on Visual Basic, and paste the code into the editor. Then, click Run to execute the macro.

How to Remove Last Two Characters in Excel with Power Query

Power Query is a powerful tool in Excel that allows you to manipulate and transform data with ease. Here's how you can use Power Query to remove the last two characters from a column:

1. Go to the Data tab in Excel and click on From Table/Range.
2. Select the table or range you want to edit and click OK.
3. In the Power Query editor, click on the column you want to edit and then click on the Transform tab.
4. Click on the Text Column button and select Extract.
5. In the Extract Text dialog box, select the Length option and enter -2 in the Length field.
6. Click OK to apply the changes.

Using Find and Replace to Remove Last Two Characters

If you only need to remove the last two characters from a few cells, you can use the Find and Replace feature in Excel. Here's how:

1. Select the cells you want to edit.
2. Go to the Home tab in Excel and click on Find & Select.
3. Click on Replace.
4. In the Find what field, enter the last two characters you want to remove.
5. Leave the Replace with field blank.
6. Click Replace All to apply the changes.

Remove Last Two Characters in Excel with Flash Fill

Flash Fill is a feature in Excel that allows you to quickly fill a range of cells with a formula or pattern. Here's how you can use Flash Fill to remove the last two characters from a column:

1. Select the cell below the column you want to edit.
2. Go to the Data tab in Excel and click on Flash Fill.
3. In the Flash Fill dialog box, select the Range option and enter the formula =LEFT(A1, LEN(A1)-2).
4. Click Flash Fill to apply the changes.

We hope this article has helped you learn how to remove the last two characters from a cell, range of cells, or column in Excel. Whether you're using formulas, VBA macros, Power Query, or the Find and Replace feature, there are many ways to achieve this task. If you have any more questions or need further assistance, feel free to ask in the comments below.

Gallery of Remove Last 2 Characters In Excel Instantly