Al Ikram Amit
There are instances when we need to remove the country code while working with a collection of phone numbers in Excel. We may exclude those country code prefixes from our dataset to make the work easier. In this post, we’ll examine four quick and simple methods on the topic of how to remove prefix 91 in Excel using the RIGHT, LEN, REPLACE, MID, and SUBSTITUTE functions, among other options.
Download Practice Workbook
You can download the practice workbook from the following download button.
4 Simple Methods to Remove Prefix 91 in Excel
Let’s assume column B has a list of phone numbers that begin with 91. By using the RIGHT, LEN, REPLACE, MID, SUBSTITUTE, and other functions, we will get rid of the prefix 91. Here is a summary of the dataset we will be using for this job.
1. Using REPLACE Function to Remove Prefix 91
We can eliminate any cell’s prefix using the REPLACE function. To use Excel’s REPLACE feature to remove the prefix 91 from phone numbers. To learn, kindly adhere to the procedures below.
📌 Steps:
- Now, we will select cell C5 from our dataset as the empty cell into which you will enter the REPLACE function.
- Select cell C5, then enter the following equation in the Formula Bar:
=REPLACE(B5,1,2,"")
Formula Explanation:
- Here, B5 refers to the cell from which you wish to delete the country code 91 prefix,
- 1 designates that the replacement will happen from the first two characters of your number,
- 2 designates the first two letters to remove, and
- (” “) designates the character you are deleting.
- Thus, by pressing Enter on your keyboard, you will be able to obtain the REPLACE function’s return value, which is 2212345678.
- After that, if you move your cursor to cell C5‘s bottom-right corner, an autofill prompt will appear. Drag the AutoFill symbol downward at this point.
- After dragging the autofill option downward we will see the final data that is shown in the image below.
Read More: How to Remove Prefix in Excel (6 Methods)
2. Applying RIGHT and LEN Functions to Remove Prefix 91
Now in this method, we will use RIGHT and LEN functions to demonstrate on the topic of how to remove the prefix 91 in excel. So, please stay with us and kindly adhere to the process below.
📌 Steps:
- Now, we will select cell C5 from our dataset as the empty cell into which you will enter the RIGHT and LEN functions.
=RIGHT(B5,LEN(B5)-2)Formula Explanation:
- B5 refers to the cell from which you wish to delete the country code 91 prefix,
- RIGHT function indicates that the characters of the text will be taken from the last character.
- LEN(B5) counts the characters of the cell B5 which is 12.
- LEN(B5)-2 indicates that the resultant text will have (12-2) or 10 characters without the first two characters of the referred text (B5), which is 2212345678.
- After inserting the formula, we will press Enter.
- And the result will look like the image below.
- At this moment, we have to use the same formula for the remaining cells.
- To do so, drag the fill handle down to the last cell.
- Finally, we got the result that is shown in the image below which will appear after dragging the autofill option downward.
Read More: How to Add Prefix Without Formula in Excel (2 Easy Ways)
3. Combining MID and LEN Functions to Remove Prefix 91
We have already seen the easy method that will do our task which is removing the prefix 91 from the phone number. However, in this method, we will use MID and LEN Functions to demonstrate on the topic of how to remove the prefix 91 in excel. So, please stay tuned and kindly adhere to the steps below.
📌 Steps:
- Now, we will select cell C5 from our dataset as the empty cell into which you will enter the MID and LEN functions.
=MID(B5,3,LEN(B5)-2)Formula Explanation:
- Here, B5 refers to the cell from which you wish to delete the country code 91 prefix,
- 3 designates the first two letters to be removed, starting from the third character of the number.
- LEN(B5)-2 indicates that the resultant text will have (12-2) or 10 characters without the first two characters of the referred text (B5), which is 2212345678.
- Now just press Enter.
- And the result will look like the image below.
- After that, we will apply the same formula to the rest of the cells.
- To complete the process, drag the fill handle down to copy the formula for the rest of the cells.
- Lastly, the final data that is shown in the image below will appear after dragging the autofill option downward.
Read More: How to Add Prefix 0 in Excel (7 Easy Methods)
4. Applying SUBSTITUTE Function to Remove Prefix 91
We have already seen three easy methods in the previous sections. Moreover, we can remove prefix 91 by applying the SUBSTITUTE function by following the necessary steps in the below section.
📌 Steps:
- Now, we will select cell C5 from our dataset as the empty cell into which you will enter the SUBSTITUTE function.
=SUBSTITUTE(B5,"91","",1)Formula Explanation:
- Here, B5 refers to the cell from which you wish to delete the country code 91 prefix,
- “91” indicates that 91 will be replaced
- (” “) designates the character 91 that you are deleting.
- 1 indicates that only the first appearance of “91” will be substituted.
- Thus, by pressing Enter on your keyboard, you will be able to obtain a value, which is 2212345678.
- Now, press Enter.
- And the final data will look like the below image.
- Now we have to apply the same formula to the rest of the cells.
- To do so, drag the fill handle down to copy the formula for the rest of the cells.
- Finally, the final data that is shown in the image below will appear after dragging the autofill option downward.
Conclusion
Follow these steps and stages on how to remove prefix 91 in excel. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.
