Afia Kona
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.
Download Workbook
3 Methods to Replace Text after Specific Character in Excel
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.
Method-1: Using Find and Replace Option 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: Excel VBA to Find and Replace Text in a Column (2 Examples)
Similar Readings
- Excel VBA: How to Find and Replace Text in Word Document
- How to Replace Text in Excel Formula (7 Easy Ways)
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
- How to Replace Text in Selected Cells in Excel (4 Simple Methods)
Method-2: Using Excel REPLACE Function to Replace 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 between Two Characters in Excel (3 Easy Ways)
Method-3: Using SUBSTITUTE Formula
In this method, we will use the SUBSTITUTE function to replace the state name in the Address column with 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: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
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 know us in the comment section.
Related Articles
- Excel VBA to Replace Blank Cells with Text (3 Examples)
- How to Replace Text with Blank Cell in Excel (5 Simple Methods)
- Excel VBA: How to Replace Text in String (5 Effective Ways)
- Excel Formula to Replace Text with Number (5 Examples)
- How to Replace Text with Carriage Return in Excel (4 Smooth Approaches)
- Excel VBA: Open Word Document and Replace Text (6 Examples)
