While working with Microsoft Excel sheets, you might need to remove the first few characters, or last few characters, or both from the text. Removing the first few characters from a column of texts is useful when you need to remove titles (Eg. Dr., Lt.). Similarly, removing the last few characters could be useful while removing phone numbers after names. In this article, spaces are counted as characters.
Remove text before or after a specific character in Excel
This post will show you how to remove the first or last few characters or certain position characters from the text in Microsoft Excel. We will cover the following topics:
- Remove the first few characters from a column of texts
- Remove the last few characters from a column of texts
- Remove both the first few & last few characters from a column of texts.
Remove the first few characters from a column of texts
The syntax to remove the first few characters from a column of texts is:
=RIGHT(<First cell with full text>, LEN(<First cell with full text>)-<Number of characters to be removed>)
Where <First cell with full text> is cell location of the first cell in the column with the full texts. <Number of characters to be removed> are the number of characters which you intend to remove from the left side of the text.
Eg. If we have a column with full text from cell A3 to A7 and need the text after removing the first 2 characters in column C, the formula would be:
=RIGHT(A3, LEN(A3)-2)
Write this formula in cell C3. Hit Enter, and it will display the text in cell A3 without the first 2 characters in cell C3. Click anywhere outside the cell C3 and then back in the cell C3 to highlight the Fill option. Now drag the formula to cell C7. This will give the texts without the first 2 characters in column C for the initial texts in columns A.
Remove the last few characters from a column of texts
The syntax to remove the last few characters from a column of texts is:
=LEFT(<First cell with full text>, LEN(<First cell with full text>)-<Number of characters to be removed>)
In this formula, <First cell with full text> is cell location of the first cell in the column with the full texts. <Number of characters to be removed> are the number of characters which you intend to remove from the right side of the text.
Eg. Let us consider a case in which we have a column with full texts from cell A3 to A7 and need the text after removing the last 9 characters in column D; the formula would be:
=LEFT(A3, LEN(A3)-9)
Now, write this formula in cell D3. Hit Enter, and it will display the text in cell A3 without the last 9 characters in cell D3. Click anywhere outside the cell D3 and then back in the cell D3 to highlight the Fill option. Now drag the formula to cell D7. This will give the texts without the last 9 characters in column D for the initial texts in columns A.
Remove both the first few & last few characters from a column of texts
If you intend to remove both the first few and last few characters from a column of texts, the syntax of the formula would be as follows:
=MID(<First cell with full text>,<Number of characters to be removed from the left side plus one>,LEN(<First cell with full text>)-<Total number of characters you intend to remove>)
Eg. If we have a column with full texts in column A from cell A3 to A7 and need the texts without the first 2 characters and last 9 characters in column E from cell E3 to E7, the formula would become:
=MID(A3,3,LEN(A3)-11)
Write this formula in cell E3. Hit Enter, and it will display the text in cell A3 without the first 2 and last 9 characters in cell E3.Click anywhere outside the cell E3 and then back in the cell E3 to highlight the Fill option. Now drag the formula to cell E7. This will give the texts without the first 2 and last 9 characters in column E for the initial texts in columns A.
I hope this post helps you remove the first or last few characters or certain position characters from the text in Microsoft Excel.
