Column B has a list of phone numbers that begin with 91. We’ll remove the prefix.
Method 1 – Using the REPLACE Function to Remove Prefix 91
Steps:
- Select cell C5.
- Enter the following formula in the Formula Bar:
=REPLACE(B5,1,2,"")
Formula Explanation:
- 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 character,
- 2 designates how many characters are removed
- (“”) designates the characters you’re inserting instead.
- If you move your cursor to cell C5‘s bottom-right corner, an AutoFill icon will appear.
- Drag the AutoFill symbol down.
- Here’s the result.
Read More: How to Remove Prefix in Excel
Method 2 – Applying RIGHT and LEN Functions to Remove Prefix 91
Steps:
- Select cell C5 and enter the following formula:
=RIGHT(B5,LEN(B5)-2)Formula Explanation:
- B5 refers to the cell from which you wish to delete the country code 91 prefix,
- The RIGHT function indicates that the characters of the text will be taken from the last character.
- LEN(B5) counts the number of 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).
- Press Enter.
- Drag the fill handle down to the last cell.
- Here’s the result.
Method 3 – Combining MID and LEN Functions to Remove Prefix 91
Steps:
- Select cell C5 and insert the following formula:
=MID(B5,3,LEN(B5)-2)Formula Explanation:
- 3 designates that the MID function will start extracting from the third character.
- LEN(B5)-2 indicates that the resulting text will have (12-2) or 10 characters without the first two characters of the referred text (B5)
- Press Enter.
- Drag the fill handle down to copy the formula for the rest of the cells.
- Here’s the result.
Method 4 – Applying the SUBSTITUTE Function to Remove Prefix 91
Steps:
- Select cell C5 and insert the following formula:
=SUBSTITUTE(B5,"91","",1)Formula Explanation:
- “91” indicates that 91 will be replaced
- (“”) designates that the replacement is empty
- 1 indicates that only the first appearance of “91” will be substituted
- Press Enter.
- Drag the fill handle down to copy the formula for the rest of the cells.
- Here’s the result.
Download the Practice Workbook
Related Articles
- How to Add Suffix in Excel
- How to Add Suffix Without Formula in Excel
- How to Add Text Suffix with Custom Format in Excel
- How to Add Prefix Without Formula in Excel
- How to Add Text Prefix with Custom Format in Excel
- How to Add Prefix to Entire Column in Excel
<< Go Back to Suffix and Prefix | Text Formatting | Learn Excel
