How to Replace Special Characters in Excel (6 Ways) - ExcelDemy (2023)

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.

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 special character # with Empty which has been given in the below screenshot.

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.

• 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 and the return is 4227.

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

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

Read More: How to Find and Replace Using Formula in Excel (4 Examples)

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

Read More: How to Use the Substitute Function in Excel VBA (3 Examples)

Similar Readings

• Excel VBA: How to Find and Replace Text in Word Document
• Replace Text of a Cell Based on Condition in Excel (5 Methods)
• How to Add Text Before a Formula in Excel (5 Easy Ways)
• Find And Replace Multiple Values in Excel (6 Quick Methods)
• How to Replace Text in Excel Formula (7 Easy Ways)

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.

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

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

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 functions in the Formula Bar are,

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

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

Read More: How to Find and Replace in Excel Column (6 Ways)

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

• 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 = StrEnd Function

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

• Now, press Enter on your keyboard and you will 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 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.

Read More: How to Find and Replace Asterisk (*) Character 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

• Substitute Multiple Characters in Excel (6 Ways)
• Add Text and Formula in the Same Cell in Excel (4 Examples)
• Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
• How to Find and Replace Multiple Words at Once in Excel (7 Methods)
• [Fixed!] Excel Find and Replace Not Working (6 Solutions)
• How to Find and Replace within Selection in Excel (7 Methods)