How to Replace Special Characters in Excel (6 Ways)

When working with text data in Excel, we may need to replace specific characters that are superfluous. To simplify the task, we’ll replace those characters from your data. Today, in this article, we’ll look at the seven quick and easy techniques that Excel replace special characters with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Suitable Ways to Replace Special Characters in Excel

Let’s say, we have different Product Codes for different Products in Column C which are started with a special character #, and the name of the products are given in Column B. Here’s an overview of the dataset for our today’s task.

Apply the Find & Select Command to Replace Special Characters in Excel


1. Apply the Find & Select Command to Replace Special Characters in Excel

In this method, we’ll learn how to replace special characters in Excel by using the Find & Select Command. Here, we want to replace # with empty from our dataset. Let’s follow the instructions below.

Steps:

  • From your Home tab, go to,

Home → Editing → Find & Select → Replace

Apply the Find & Select Command to Replace Special Characters in Excel

  • After clicking on the Replace option, a Find and Replace window pops up.

Apply the Find & Select Command to Replace Special Characters in Excel

  • From the Find and Replace window, type # in the Find what box and Replace with box keeps
  • After that, click on the Replace All box.

  • Now, a new dialog box named Microsoft Excel appears in front of you showing with All done. We made & replacements.
  • After that press OK.

Apply the Find & Select Command to Replace Special Characters in Excel

  • After completing the above process, you will be able to replace special character # with Empty which has been given in the below screenshot.

Apply the Find & Select Command to Replace Special Characters in Excel

Read more: How to Find And Replace Values Using Wildcards in Excel


2. Insert the REPLACE Function to Replace Special Characters in Excel

You can use the REPLACE function to replace any character from any cell. To replace special characters in Excel by using the REPLACE function follow the steps below to learn!

Steps:

  • First of all, select an empty cell where we will type the REPLACE function, from our data we’ll select cell D5.

Insert the REPLACE Function to Replace Special Characters in Excel

  • After selecting cell D5, type the following formula in the Formula Bar,
=REPLACE(C5,1,1,"")
  • Here, C5 refers to the cell from which you want to replace the special character, First 1 indicates that you want to replace the character from the first character of your text, 2nd 1 indicates that you want to replace one character, and (” ”) indicates that you are removing that character.

  • Now, press Enter on your keyboard and you will be able to get the return of the REPLACE function and the return is 4227.

Insert the REPLACE Function to Replace Special Characters in Excel

  • After that, place your cursor on the Bottom-Right side of cell D5 and an autoFill sign pops us. Now, drag the autoFill sign downward.

Insert the REPLACE Function to Replace Special Characters in Excel

  • While completing the above process, you will be able to replace # with Empty which has been given in the screenshot.

Insert the REPLACE Function to Replace Special Characters in Excel


3. Use of the SUBSTITUTE Function to Replace Special Characters in Excel

Using the SUBSTITUTE Function is another way to replace special characters in Excel. From our dataset, we want to replace # with Empty by using the SUBSTITUTE Function. Follow the instructions below to learn!

Steps:

Use of the SUBSTITUTE Function to Replace Special Characters in Excel

  • After that, type the SUBSTITUTE Function in the Formula Bar. The function is,
=SUBSTITUTE(C5, "#", "")

Use of the SUBSTITUTE Function to Replace Special Characters in Excel

  • After typing the SUBSTITUTE Function in the Formula Bar, press Enter on your keyboard and you will be able to get the output of the function, the output is 4227 which is replacing special characters.

  • Now, place your cursor on the Bottom-Right side of cell D5 and an autoFill sign pops us. Now, drag the autoFill sign downward, and you will get the output of the SUBSTITUTE Function.

Use of the SUBSTITUTE Function to Replace Special Characters in Excel


Similar Readings:


4. Perform the Flash Fill Command to Replace Special Characters in Excel

The easiest way is to replace special characters in Excel by using Flash Fill Command. To replace special characters by using Flash Fill Command, follow the instructions below.

Steps:

  • First, select cell D5 and manually type the Code of the Product named Eraser without special character.

Perform the Flash Fill Command to Replace Special Characters in Excel

  • Now, from Home Tab, go to,

Home → Editing → Fill → Flash Fill

Perform the Flash Fill Command to Replace Special Characters in Excel

  • Finally, you will get all the product codes without the special characters by pressing on the Flash Fill option.


5. Apply the RIGHT and LEN Functions to Replace Special Characters in Excel

In this method, we’ll learn how to replace special characters in Excel By using the RIGHT and the LEN functions. Let’s follow the steps below to learn!

Steps:

  • First, select cell D5 to replace special characters from cell C5.

Apply the RIGHT and LEN Functions to Replace Special Characters in Excel

  • After that, type the RIGHT and the LEN functions in the Formula Bar. The functions in the Formula Bar are,
=RIGHT(C5,LEN(C5)-1)
  • Here, C5 refers to the cell from which you want to replace the character, the RIGHT function indicates that the characters of the text will be taken from the last character, and LEN(C5)-1 indicates that the resultant text will be without the first character of the referred text (C5).

  • Now, press Enter on your keyboard and you will get the output of the functions. The output of the functions is 4227.

  • After pressing Enter on your keyboard, place your cursor on the Bottom-Right of cell D5 and instantly an autoFill sing appears in front of you.

Apply the RIGHT and LEN Functions to Replace Special Characters in Excel

  • After that, drag the autoFill sing downward and you will be able to replace special characters from column C.

Apply the RIGHT and LEN Functions to Replace Special Characters in Excel


6. Run a VBA Code to Replace Special Characters in Excel

We can replace special characters by running a VBA Code. It is the easiest way to replace special characters in Excel. Please follow the instruction below to learn!

Step 1:

  • First of all, press the ALT + F11 keys on your keyboard to open the Microsoft Visual Basic for Applications
  • From the Microsoft Visual Basic for Applications window, go to,

Insert → Module

Run a VBA Code to Replace Special Characters in Excel

  • After clicking on the Module, a new window named Module 1 will appear in front of you instantly.

Step 2:

  • In the Module 1 window, Paste the following Code.
Function ReplaceSpecial(Str As String) As String
'updatedbyExceldemy 0043
    Dim Char As String
    Dim L As Long
    Char = "#$%()^*&"
    For L = 1 To Len(Char)
        Str = Replace$(Str, Mid$(Char, L, 1), "")
    Next
    RemoveSpecial = Str
End Function

Run a VBA Code to Replace Special Characters in Excel

  • Then save and close this code, go back to the worksheet.

Step 3:

  • Now, select cell D5 to type the formula ReplaceSpecial.

  • After that type the ReplaceSpecial formula in the Formula Bar. The formula in the Formula Bar is,
=ReplaceSpecial(C5)

Run a VBA Code to Replace Special Characters in Excel

  • Now, press Enter on your keyboard and you will get the output 4227 as the ReplaceSpecial function in cell D5.

Run a VBA Code to Replace Special Characters in Excel

  • Then, place your cursor on the Bottom-Right of cell D5 and instantly an autoFill sign appears in front of you, and drag it downward.
  • After dragging the autoFill sing downward, you will be able to get the output of the ReplaceSpecial function to the entire column D which has been replaced with special characters.

Run a VBA Code to Replace Special Characters in Excel


Things to Remember

👉 While using the Flash Fill Command type the Code of Eraser manually and then go to,

Home → Editing → Fill → Flash Fill

👉 If Developer Menu does not appear in the Menu  Bar, press ALT + F11 keys on your keyboard to open the Microsoft Visual Basic for Applications window.


Conclusion

I hope all of the suitable methods mentioned above to replace special characters will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Further Readings

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo