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.

Replace Special Characters in Excel: 6 Suitable Ways

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

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

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

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

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

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.

• After selecting cell D5, type the following formula in the Formula Bar,

• 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, which is 4227.

• After that, place your cursor on the bottom right side of cell D5, and an autoFill sign pops up. Now, drag the autoFill sign downward.

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

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:

• Select cell D5 first to apply the SUBSTITUTE function.

• After that, type the SUBSTITUTE function in the Formula Bar. The function is,

• 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 up. Now, drag the autoFill sign downward, and you will get the output of the SUBSTITUTE function.

Similar Readings

• Replace Text of a Cell Based on Condition in Excel
• How to Replace Text in Excel Formula

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

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

Steps:

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

• Now, from Home tab, go to,

Home → Editing → Fill → Flash Fill

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

• After that, type the RIGHT, and the LEN functions in the Formula Bar. The formula is:

• 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 sign appears before you.

• After that, drag the autoFill sign downward, and you can replace special characters from column C.

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 instructions below to learn!

Step 1:

• First, 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

• 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

• Then save and close this code and 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,

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

• Then, place your cursor on the bottom right of cell D5, and instantly, an autoFill sign appears in front of you and drags it downward.
• After dragging the autoFill sign downward, you can get the output of the ReplaceSpecial function to the entire column D, which has been replaced with special characters.

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

Download Practice Workbook

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

Conclusion

I hope all the suitable methods mentioned above to replace special characters will 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

• Substitute Multiple Characters in Excel
• How to Show Dash Instead of Zero in Excel