Md. Abdur Rahim Rasel
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.
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.
- After that, type the RIGHT and the LEN functions in the Formula Bar. The functions in the Formula Bar are,
=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).
- 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.
- Hence, drag the autoFill sing downward and you will be able to remove the prefix from column C.
Read More: How to Remove Prefix 91 in Excel (4 Easy Ways)
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.
- 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.
- 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.
Read More: How to Add Prefix Without Formula in Excel (2 Easy Ways)
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.
- Hence, type the following formula in the Formula Bar,
=MID(C5, 3, LEN(C5)-1)
- 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.
- When you release dragging the autoFill sign, you will get your desired output that has been given below screenshot.
Read More: How to Add Prefix 0 in Excel (7 Easy Methods)
Similar Readings
- How to Add Suffix in Excel (4 Easy Ways)
- Remove Panes in Excel (4 Methods)
- Add Suffix Without Formula in Excel (2 Easy Methods)
- How to Remove Header and Footer in Excel (6 Methods)
- Remove Comments in Excel (7 Quick Methods)
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.
- After that, from Home Tab, go to,
Home → Editing → Fill → Flash Fill
- 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:
- Select cell D5 first to apply the SUBSTITUTE Function.
- After that, type the SUBSTITUTE Function in the Formula Bar. The function is,
=SUBSTITUTE(C5, "ID", "")
- 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.
Step 2:
- Further, place your cursor on the Bottom-Right side of cell D5 and an autoFill sign pops us.
- 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
- After clicking on the Replace option, a Find and Replace window pops up.
- 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.
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
- Remove Compatibility Mode in Excel (2 Easy Ways)
- How to Add Text Suffix with Custom Format in Excel (5 Examples)
- Remove Watermark in Excel (3 Ways)
- Sort Numbers with Letter Suffix in Excel (4 Useful Methods)
- How to Remove Table Functionality in Excel (3 Methods)
- Remove Last Digit in Excel (6 Quick Methods)
- How to Remove Year from Date in Excel (5 Easy Ways)
