While working with Microsoft Excel sheets, you might need to remove the first few characters, the 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 text
- Remove the last few characters from a column of text
- Remove both the first few & last few characters from a column of text.
Remove the first few characters from a column of text
The syntax to remove the first few characters from a column of text 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 the cell location of the first cell in the column with the full texts. <Number of characters to be removed> are the number of characters 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 two 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 two characters in cell C3. Click anywhere outside cell C3 and then back in cell C3 to highlight the Fill option. Now drag the formula to cell C7. This will give the texts without the first two characters in column C for the initial texts in column A.
Fix: Excel not auto calculating formulas.
Remove the last few characters from a column of text
The syntax to remove the last few characters from a column of text 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 the cell location of the first cell in the column with the full text. <Number of characters to be removed> are the number of characters 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 nine 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 nine characters in cell D3. Click anywhere outside cell D3 and then back in cell D3 to highlight the Fill option. Now drag the formula to cell D7. This will give the texts without the last nine characters in column D for the initial texts in column 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 text, 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 two characters and the 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 two and last nine characters in cell E3. Click anywhere outside cell E3 and then back in cell E3 to highlight the Fill option. Now drag the formula to cell E7. This will give the texts without the first two and last nine characters in column E for the initial texts in column A.
I hope this post helps you remove the first or last few characters or certain position characters from the text in Microsoft Excel.
Leave a Reply