How to remove text before or after a specific character in Excel

When working with Microsoft Excel worksheets, you may need to delete the first few characters, the last characters, or both of the text. Removing the first few characters from a text column is useful when you need to delete titles (for example, Dr., Lt.). Likewise, removing the last characters might be useful when deleting phone numbers after the names. In this article, spaces are counted as characters.

Delete text before or after a specific character in Excel

This article will show you how to delete the first or last characters or some positional characters from text in Microsoft Excel. We will cover the following topics:

  1. Delete the first few characters from a text column
  2. Delete the last characters from a text column
  3. Deletes the first two and last characters of a text column.

Delete the first few characters from a text column

The syntax for removing the first few characters from a text column is as follows:

=RIGHT(, LEN()-)

Or Cell location of the first cell of the column with the complete texts. are the number of characters you want to delete on the left side of the text.

For example. If we have a column with the full text of cell A3 to A7 and need the text after removing the first 2 characters from column C, the formula would be:

=RIGHT(A3, LEN(A3)-2)

Delete text before or after a specific character in Excel

Write this formula in cell C3. Press Enter to display the text of cell A3 without the first 2 characters of cell C3. Click anywhere outside cell C3 and then in cell C3 to highlight the Fill option. Now drag the formula into cell C7. This will give the texts without the first 2 characters of column C for the initial texts of columns A.

Delete the last characters from a text column

The syntax for removing the last characters from a text column is as follows:

=LEFT(, LEN()-)

In this formula, Cell location of the first cell of the column with the complete texts. are the number of characters you want to delete on the right side of the text.

For example. Consider a case in which we have a column with the full texts of cell A3 to A7 and need the text after removing the last 9 characters from column D; the formula would be:

=LEFT(A3, LEN(A3)-9)

Delete the last characters

Now write this formula in cell D3. Press Enter to display the text of cell A3 without the last 9 characters of cell D3. Click anywhere outside cell D3, and then return to cell D3 to highlight the Fill option. Now drag the formula into cell D7. This will give the texts without the last 9 characters of column D for the initial texts of columns A.

Delete the first two and last characters of a text column

If you want to delete the first and last characters of a text column, the syntax of the formula is as follows:

=MID(,,LEN()-)

For example. If we have a column with the complete texts in column A of cell A3 to A7 and if we need the texts without the first 2 characters and the last 9 characters of column E of cells E3 to E7, the formula becomes:

=MID(A3,3,LEN(A3)-11)

Delete the first two and last characters

Write this formula in cell E3. Press Enter to display the text of cell A3 without the first 2 and last 9 characters of cell E3.Click anywhere outside cell E3, and then again in cell E3 to highlight Fill option. Now drag the formula into cell E7. This will give the texts without the first 2 and last 9 characters of column E for the initial texts of columns A.

I hope this article will help you delete the first or last characters or some positional characters of the text in Microsoft Excel.

Leave a Comment