If you want to replace text after a specific character in Excel, this article is for you. Here, we will walk you through 3 simple, easy, and effective methods so that you can do the task effortlessly.
The following Student List table shows ID No, Name, and Address columns. We will use 3 methods to replace text after a specific character in this table. Here, we used Excel 365. You can use any available Excel version.
1. Using Excel Find and Replace Tool to Replace Text after Specific Character
In this method, we will use the Find and Replace option to replace comma (,) in the address column with an in.
➤ First of all, we will select the cells from F5 to F9 of the Updated Address column > go to the Home tab > click on Editing > select Find and Select > select Replace.
After that, a Find and Replace window will appear.
➤ In the Find what box, we will type a comma (,) > in the Replace with box we will give a space and type in.
➤ Afterward, click on Replace All.
A confirmation window will appear.
➤ Click OK.
Finally, we can see in the Updated Address column that the comma (,) has been replaced by in.
Read More: How to Replace Text in Selected Cells in Excel
2. Inserting Excel REPLACE Function to Alter Text after Specific Character
Here, we will use the REPLACE function to replace the state name of the Address column with the state abbreviation from the State Abbreviation column.
➤ First of all, we will type the following formula in cell G5.
=REPLACE(D5,FIND(",",D5)+2,10,E5)- FIND(“,”,D5)+2 → returns the starting position of the old text we want to replace. 2 indicates that the new text will start after 2 spaces of the comma(,) within the cell D5.
Output: 9
- REPLACE(D5,FIND(“,”,D5)+2,10,E5) → replaces the value returned from FIND(“,”,D5)+2 with E5 within cell D5. 10 is the number of characters in the old text.
➤ After that, press ENTER.
We can see the result in cell G5.
➤ After that, we will drag down the formula with the Fill Handle tool.
Finally, in the Updated Address column, we can see the state name has been replaced with the state abbreviation.
Read More:How to Replace Text with Blank Cell in Excel
3. Replacing Text After Specific Character with SUBSTITUTE Formula
In this method, we will use the SUBSTITUTE function to replace the state name in the Address column with the state abbreviation from the State Abbreviation column.
➤ First of all, we will type the following formula in cell G5.
=SUBSTITUTE(D5,MID(D5,FIND(",",D5)+1,LEN(D5)),E5,1)- FIND(“,”,D5)+1 → returns the starting position of the old text we want to replace. 1 indicates that the new text will start after 1 space of the comma(,) within cell D5.
Output: 8
- LEN(D5) → determines the number of characters of cell D5.
Output: 16
- MID(D5,FIND(“,”,D5)+1,LEN(D5) → returns the old text we want to replace in cell D5.
Output: “New York”
- SUBSTITUTE(D5,MID(D5,FIND(“,”,D5)+1,LEN(D5)),E5,1) → substitutes the value returned from MID(D5,FIND(“,”,D5)+1,LEN(D5) which is “New York” with the text in the cell E5 which is “NY” within cell D5.
➤ After that, press Enter.
We can see the result in cell G5.
➤ Afterward, we will drag down the formula with the Fill Handle tool.
Finally, in the Updated Address column, we can see the result.
Read More: Excel Formula to Replace Text with Number
Download Workbook
Conclusion
Here, we tried to show you 3 methods to replace text after a specific character in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, feel free to let us know in the comment section.
Related Articles
- How to Replace Text with Carriage Return in Excel
- How to Replace Text between Two Characters in Excel
<< Go Back to Excel REPLACE Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
