Learn How to Remove the Last Two Characters from a String in Excel

In data management and analysis, a frequent requirement in Excel involves manipulating text strings, particularly truncating them to meet specific formatting needs. Whether cleaning imported data or preparing variables for analysis, removing a fixed number of characters from the end of a string is a straightforward task when utilizing the appropriate combination of native Excel functions.

To efficiently remove the last two characters from any text value, we employ a powerful pairing: the LEFT function, which extracts characters starting from the left, and the LEN function, which determines the total character count. This synergy allows us to dynamically calculate the desired length of the resulting string, regardless of the original text’s length.

The resulting formula structure is concise yet highly effective. If your target string is located in cell A2, the syntax required to perform this truncation is as follows:

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

This specific formula instructs Excel to calculate the total length of the content in cell A2, subtract two characters from that total, and then return only that calculated number of characters, starting from the leftmost position. This ensures that the original text is preserved, minus the final two characters.

Understanding this fundamental approach is key to mastering text manipulation in spreadsheets. The following detailed example illustrates exactly how to implement and apply this technique across a data set.

Applying the Solution: A Practical Example

Consider a scenario where you are managing a database containing team identifiers. For this demonstration, we are working with a list of basketball team names in Column A, where each entry includes two extraneous characters (perhaps a year code or a numerical suffix) at the very end. Our objective is to clean this data by removing those trailing characters.

The initial dataset, positioned in Column A, appears as follows:

To achieve the desired data cleanliness, we will apply the combined LEFT and LEN function formula to the first data point. We initiate the process by typing the function into cell B2, which corresponds to the first entry in our dataset (cell A2).

The formula entered into cell B2 is:

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

After successfully calculating the result for the first cell, the versatility of Excel allows us to quickly apply this calculation to the entire range. We simply use the fill handle—the small square at the bottom-right corner of cell B2—to click and drag the formula down, automatically adjusting the cell reference (A2 becomes A3, A4, and so on) for every subsequent row in Column B.

As clearly demonstrated in the resulting image, Column B now accurately displays the standardized team names. The original text strings from Column A have been processed, and the final two unwanted characters have been systematically removed from every entry, achieving our data standardization goal.

Deconstructing the Formula: LEFT and LEN Functions Explained

To truly appreciate the power of this text manipulation technique, it is helpful to examine the roles of the two distinct functions involved. We utilized the following structure, targeting the content of cell A2:

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

The operation begins with the inner function, LEN(). This function is fundamental in Excel for text handling, as its sole purpose is to return the numerical count of all characters contained within a specified text string. In our formula, LEN(A2) calculates the total length of the text in cell A2. By subtracting -2 from this result, we determine precisely how many characters we need to keep, effectively excluding the last two.

The result of this calculation then feeds directly into the primary function, LEFT(). The LEFT function requires two arguments: the source text and the number of characters to extract from the start (left side). Since the second argument we provide is LEN(A2)-2, the LEFT function systematically extracts the entire original text content, stopping exactly two characters short of the end.

This nested structure ensures that the formula is dynamic. It works correctly regardless of whether the original string is five characters long or fifty characters long, as the length is calculated anew for every cell reference. This makes it a robust solution for large datasets requiring uniform truncation.

Handling Text Strings and Character Integrity

When working with text manipulation functions like LEFT and LEN, it is crucial to understand how Excel defines a character. A common pitfall for users is overlooking hidden or visible blank spaces. In the context of the LEN() function, every single element, including standard letters, numbers, punctuation, and crucially, blank spaces, contributes to the overall length count of the string.

If your string contains trailing spaces (spaces at the end of the text), these spaces will be counted as the “last two characters” and will be the characters removed by our current formula. If your intention is to remove content that is not a space, but your data contains unintended trailing spaces, you must first clean the data using the TRIM() function.

A modified workflow to ensure accuracy involves nesting the TRIM function inside the LEFT function. For instance, =LEFT(TRIM(A2), LEN(TRIM(A2))-2) would first eliminate all unnecessary leading and trailing spaces before calculating the length and performing the truncation. This is generally recommended for professional data cleansing operations.

Further Learning and Related Operations

Mastering text extraction often requires familiarity with other specialized functions beyond LEFT and LEN. For those interested in expanding their proficiency in advanced text manipulation within Excel, the following related tutorials offer explanations of common operations, such as extracting characters from the middle of a string or using variable lengths:

• Excel: A Formula for MID From Right
• Excel: How to Use MID Function for Variable Length Strings