How to Remove Prefix in Excel (6 Methods)

While working with a large dataset in Excel, sometimes we need to remove prefixes of data that are superfluous. To simplify the task, we can remove those prefixes from our dataset. Today, in this article, we’ll look at the six quick and easy techniques including the RIGHT, LEN, REPLACE, MID, and SUBSTITUTE Functions, and so on to remove prefix in Excel with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Suitable Ways to Remove Prefix in Excel

Let’s say, we have some student IDs which start with ID and the Name of the corresponding IDs in column C and column B respectively. We will remove the prefix named ID of the IDs by applying the RIGHT, LEN, REPLACE, MID, and SUBSTITUTE Functions, and so on. Here’s an overview of the dataset for our today’s task.

how to remove prefix in excel


1. Apply the RIGHT and LEN Functions to Remove Prefix in Excel

In this method, from our dataset, we’ll learn how to remove ID as the prefix of a student’s ID in Excel by using the RIGHT and the LEN functions. Let’s follow the steps below to learn!

Step 1:

  • First, select cell D5 to replace special characters from cell C5.

Apply the RIGHT and LEN Functions to Remove Prefix in Excel

=RIGHT(C5,LEN(C5)-2)
  • Here, C5 refers to the cell from which you want to remove the prefix of the student’s ID, the RIGHT function indicates that the characters of the text will be taken from the last character, and LEN(C5)-2 indicates that the resultant text will be without the first two characters of the referred text (C5).

Apply the RIGHT and LEN Functions to Remove Prefix in Excel

  • Now, press Enter on your keyboard and you will get the output of the functions. The output of the functions is 67616.

Step 2:

  • After pressing Enter on your keyboard, place your cursor on the Bottom-Right of cell D5 and instantly an autoFill sing appears in front of you.

Apply the RIGHT and LEN Functions to Remove Prefix in Excel

  • Hence, drag the autoFill sing downward and you will be able to remove the prefix from column C.


2. Insert the REPLACE Function to Remove Prefix in Excel

You can use the REPLACE function to remove the prefix from any cell. To remove ID as the prefix of students Identification in Excel by using the REPLACE function. Please follow the steps below to learn!

Step 1:

  • First of all, select an empty cell where we will type the REPLACE function, from our dataset we’ll select cell D5.

Insert the REPLACE Function to Remove Prefix in Excel

  • After selecting cell D5, type the following formula in the Formula Bar,
=REPLACE(C5,1,2,"")
  • Here, C5 refers to the cell from which you want to remove the prefix of the students’ ID, 1 indicates that you want to remove the prefix from the first two characters of your text, 2 indicates that you want to remove the first two characters, and (” ”) indicates that you are removing that character.

Insert the REPLACE Function to Remove Prefix in Excel

  • Hence, press Enter on your keyboard and you will be able to get the return of the REPLACE function and the return is 67616.

Step 2:

  • After that, place your cursor on the Bottom-Right side of cell D5 and an autoFill sign pops us. Now, drag the autoFill sign downward.

  • While completing the above process, you will be able to remove ID as the prefix of the student’s identification number which has been given in the screenshot.

Insert the REPLACE Function to Remove Prefix in Excel


3. Use of the MID and LEN Functions to Remove Prefix in Excel

Here, we’ll apply both of the MID and LEN functions to remove the prefixes in Excel. From our dataset, we are applying these two functions to remove the first two characters(ID). To remove the prefixes by using the MID and LEN functions, Please follow the below steps to learn!

Step 1:

  • To apply the MID and LEN functions, you have to select a cell first. For our work, we select cell D5.

Use of the MID and LEN Functions to Remove Prefix in Excel

  • Hence, type the following formula in the Formula Bar,
=MID(C5, 3, LEN(C5)-1)

Use of the MID and LEN Functions to Remove Prefix in Excel

  • While completing to type the formula in the Formula Bar, simply press Enter on your keyboard, and instantly you will get 67616 as the return of the functions.

Step 2:

  • Further, place your cursor on the Bottom-Right side of cell D5 and instantly an autoFill sign will appear in front of you. Now, drag the autoFill sign downward.

Use of the MID and LEN Functions to Remove Prefix in Excel

  • When you release dragging the autoFill sign, you will get your desired output that has been given below screenshot.

Use of the MID and LEN Functions to Remove Prefix in Excel


Similar Readings


4. Perform the Flash Fill Command to Remove Prefix in Excel

The easiest way is to remove the prefix in Excel by using Flash Fill Command. To remove the prefix by using Flash Fill Command, follow the instructions below.

Steps:

  • First, select cell D5 and manually type Michael’s identification number 67616 without the prefix ID.

Perform Flash Fill Command to Remove Prefix in Excel

  • After that, from Home Tab, go to,

Home → Editing → Fill → Flash Fill

Perform Flash Fill Command to Remove Prefix in Excel

  • Finally, you will get all student’s identification numbers without the ID by pressing on the Flash Fill option.


5. Apply the SUBSTITUTE Function to Remove Prefix in Excel

We can easily use the SUBSTITUTE Function to remove prefix in Excel. From our dataset, we want to remove ID by using the SUBSTITUTE Function. Follow the instructions below to learn!

Step 1:

Apply the SUBSTITUTE Function to Remove Prefix in Excel

=SUBSTITUTE(C5, "ID", "")

Apply the SUBSTITUTE Function to Remove Prefix in Excel

  • After typing the SUBSTITUTE Function in the Formula Bar, press Enter on your keyboard and you will be able to get the output of the function, the output is 67616.

Apply the SUBSTITUTE Function to Remove Prefix in Excel

Step 2:

  • Further, place your cursor on the Bottom-Right side of cell D5 and an autoFill sign pops us.

Apply the SUBSTITUTE Function to Remove Prefix in Excel

  • Now, drag the autoFill sign downward, and finally, you will get the output of the SUBSTITUTE Function.


6. Use of the Find & Replace Command to Remove Prefix in Excel

In this method, we’ll learn how to remove the prefix in Excel by using the Find & Select Command. Here, we want to remove the ID from column C. Let’s follow the instructions below.

Steps:

  • From your Home Tab, go to,

Home → Editing → Find & Select → Replace

Use of the Find & Replace Command to Remove Prefix in Excel

  • After clicking on the Replace option, a Find and Replace window pops up.

Use of the Find & Replace Command to Remove Prefix in Excel

  • From the Find and Replace window, type ID in the Find what box and Replace with box keeps
  • Hence, click on the Replace All box.

  • After that, a new dialog box named Microsoft Excel appears in front of you showing with All done. We made 10 replacements.
  • Now, press OK.

  • After completing the above process, you will be able to remove ID with Empty which has been given in the below screenshot.

Use of the Find & Replace Command to Remove Prefix in Excel

Read More: How to Find and Delete Rows in Excel (5 Ways)


Things to Remember

👉 While applying the Flash Fill Command, type the Code of Eraser manually and then go to,

Home → Editing → Fill → Flash Fill

👉 Another way is to use the Ctrl + E as the keyboard shortcut instead of Flash Fill Command.

👉 The keyboard shortcut is Ctrl + H to apply the Find & Replace method.


Conclusion

I hope all of the suitable methods mentioned above to remove prefixes will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo