How to Replace Text after Specific Character in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Replace Text after Specific Character


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.

Excel Replace Text after Specific Character

Read More: Excel VBA to Find and Replace Text in a Column (2 Examples)


Similar Readings


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.

Excel Replace Text after Specific Character

➤ 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.

Excel Replace Text after Specific Character

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.

Excel Replace Text after Specific Character

➤ 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.

Excel Replace Text after Specific Character

Finally, in the Updated Address column, we can see the result.

Excel Replace Text after Specific Character

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

Afia Kona
Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo