You can use one of the following formulas to remove a prefix from a cell in Excel:
Formula 1: Use RIGHT and LEN to Remove Prefix
=RIGHT(A2,LEN(A2)-3)
This particular example will remove the first 3 characters from the text in cell A2.
This formula is useful when the first 3 characters represent a prefix you want to remove, yet the prefix might be different for each cell in a range.
Formula 2: Use TEXTAFTER to Remove Prefix
=TEXTAFTER(A2, "EMP")
This particular example will only extract the text after the string “EMP” in cell A2.
This formula is useful when the each cell in a range has the same prefix (e.g. “EMP”) and you’d like to simply extract the text after this prefix.
The following examples show how to use each formula in practice.
Example 1: Use RIGHT and LEN to Remove Prefix
Suppose we have the following dataset that contains ID numbers along with total sales for various employees at a company:
Notice that each employee ID has a prefix of three letters before the ID numbers.
We can type the following formula into cell C2 to remove this prefix from cell A2:
=RIGHT(A2,LEN(A2)-3)
We can then click and drag this formula down to each remaining cell in column B:
Column C now contains each employee ID value in column A with the prefix removed.
Example 2: Use TEXTAFTER to Remove Prefix
Suppose we have the following dataset that contains ID numbers along with total sales for various employees at a company:
Notice that each employee ID has the same “EMP” prefix before the ID numbers.
We can type the following formula into cell C2 to remove this “EMP” prefix from cell A2:
=TEXTAFTER(A2, "EMP")
We can then click and drag this formula down to each remaining cell in column B:
Notice that the “EMP” prefix has been removed from each cell in column C.
Note: You can find the complete documentation for the TEXTAFTER function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Remove Specific Text from Cells
Excel: How to Remove Special Characters
Excel: How to Insert a Character into a String
