Table of Contents
The process of removing the first and last character from a string in Excel involves utilizing the built-in functions of the software. This can be achieved by using the LEFT and RIGHT functions to extract the desired characters, and then combining them using the CONCATENATE function. This method allows for the removal of the first and last characters, without affecting the rest of the string. Additionally, the use of the SUBSTITUTE function can also be employed to replace the first and last characters with a blank space, effectively removing them from the string. Overall, these functions provide a simple and efficient way to remove the first and last character from a string in Excel.
Excel: Remove First and Last Character from String
Often you may want to remove both the first and last character from a string in Excel.
You can use the following formula to do so:
=MID(A2,2,LEN(A2)-2)
This particular formula removes the first and last character from the string in cell A2.
For example, if cell A2 contains AA4506 then this formula would return just A450.
The following example shows how to use this formula in practice.
Example: Remove First and Last Character from String in Excel
Suppose we have the following list of employee ID’s in Excel:
Suppose we would like to remove the first and last character from each employee ID.
We can type the following formula into cell B2 to do so:
=MID(A2,2,LEN(A2)-2)
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays the employee ID’s in column A with the first and last characters removed from each employee ID.
How This Formula Works
Recall the formula that we used to remove both the first and last character from cell A2:
=MID(A2,2,LEN(A2)-2)
The MID() function in Excel extracts a specific number of characters from the middle of a string, based on a starting position and a number of characters to extract.
The LEN() function in Excel is used to find the length of a string.
Thus, our formula tells Excel to extract the characters from cell A2 starting from position 2 and going all the way to the entire length of the string minus two characters.
The end result is that we’re able to extract all of the characters from the string except for the first and last character.
This has the same effect as removing the first and last character from the string.
Note: Blank spaces in the string count as characters. You may need to first remove blank spaces to get your desired result.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: