Remove Last Two Characters From String In Excel Easily

The world of Excel can be a daunting place, especially when it comes to manipulating strings. But fear not, dear reader, for we're about to explore a simple yet effective way to remove the last two characters from a string in Excel.

Whether you're a seasoned Excel pro or a beginner, this tutorial will guide you through the process with ease. So, let's dive in and discover how to remove the last two characters from a string in Excel.

Why Remove the Last Two Characters from a String?

Before we dive into the solution, let's explore some scenarios where removing the last two characters from a string might be necessary:

• You have a list of product codes with a suffix that needs to be removed.
• You're working with dates and need to remove the last two characters to extract the year.
• You're dealing with text data that has trailing characters that need to be removed.

Method 1: Using the LEFT Function

One of the easiest ways to remove the last two characters from a string is by using the LEFT function. This function returns a specified number of characters from the left side of a text string.

Here's the syntax:

LEFT(text, [num_chars])

• text is the string you want to manipulate.
• [num_chars] is the number of characters you want to return from the left side.

To remove the last two characters from a string, you can use the following formula:

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

Assuming the string you want to manipulate is in cell A1, this formula will return all characters except the last two.

How it Works:

• LEN(A1) returns the length of the string in cell A1.
• Subtracting 2 from the length gives us the number of characters we want to return from the left side.
• The LEFT function then returns the specified number of characters from the left side of the string.

Example:

String	Formula	Result
ABCDEFG	=LEFT(A1, LEN(A1)-2)	ABCDEF

Method 2: Using the MID Function

Another way to remove the last two characters from a string is by using the MID function. This function returns a specified number of characters from the middle of a text string.

Here's the syntax:

MID(text, [start_num], [num_chars])

• text is the string you want to manipulate.
• [start_num] is the position of the first character you want to return.
• [num_chars] is the number of characters you want to return.

To remove the last two characters from a string, you can use the following formula:

=MID(A1, 1, LEN(A1)-2)

Assuming the string you want to manipulate is in cell A1, this formula will return all characters except the last two.

How it Works:

• LEN(A1) returns the length of the string in cell A1.
• Subtracting 2 from the length gives us the number of characters we want to return.
• The MID function then returns the specified number of characters from the middle of the string, starting from the first character.

Example:

String	Formula	Result
ABCDEFG	=MID(A1, 1, LEN(A1)-2)	ABCDEF

Method 3: Using VBA

If you're comfortable with VBA, you can use a simple macro to remove the last two characters from a string.

Here's an example code:

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

This macro loops through each cell in the selected range and uses the LEFT function to remove the last two characters from the string.

Conclusion

Removing the last two characters from a string in Excel can be achieved using various methods, including the LEFT function, MID function, and VBA. Choose the method that works best for you, and don't hesitate to reach out if you have any questions or need further assistance.

More String Manipulation Techniques

• Remove First Two Characters from a String in Excel
• Extract Middle Characters from a String in Excel
• Split a String into an Array in Excel

Excel String Manipulation Gallery

I hope this article has helped you learn how to remove the last two characters from a string in Excel. If you have any questions or need further assistance, please don't hesitate to ask.