How to Replace Special Characters in Excel

Replacing special characters means substituting or removing non-alphanumeric characters in an Excel spreadsheet to clean a dataset or make the dataset standard.

In this Excel tutorial, you are going to learn how to replace special characters in Excel.

For example, in the following dataset, you see the product codes are invalid because they have some unnecessary ™ characters inside them. To replace these characters, we can use the SUBSTITUTE function.

After reading this article, you will learn how to replace special characters in Excel using:

• The Find and Replace feature
• The Flash Fill feature
• Dedicated Excel functions to replace text strings
• Excel LAMBDA function and a VBA code for a set of specified characters
• A single formula to replace characters based on conditions.

We have also covered how to replace characters that you cannot type and how to replace foreign letters.

In the last section, I have shown how you can replace or remove non-printable characters.

⏷ Use Find & Replace Feature to Replace Special Characters in Excel
   ⏵Case 1: Replace All Occurrences of a Character
   ⏵Case 2: Replace a Character with Different Characters Each Time
⏷ Use Flash Fill Feature
⏷ Use Excel Functions to Replace Special Characters
⏷ Formula to Replace Special Characters Based on Conditions
⏷ Use the LAMBDA Function to Replace a Set of Special Characters
⏷ Using VBA to Replace Special Characters
⏷ Replace Characters Which You Cannot Type
⏷ Replace Foreign Letters with English Alphabets

1. Use Find & Replace Feature to Replace Special Characters in Excel

Using the Find and Replace feature, you can find a character or a string in a range of data and replace them with other characters or strings.

Here, we will learn 2 uses of the Find and Replace feature to replace special characters in Excel.

Case 1: Replacing characters from a selected range or whole worksheet/workbook.

Case 2: Replacing a character with different characters each time.

Note:

Keep in mind that this feature replaces the values within the cell. If you want to preserve the original data, make sure to back it up.

Case 1: Replace a Character from a Selected Range/Whole Worksheet/Workbook

In the following dataset, there are repeated ™ characters in C6:C12 cells. We don’t want them in our final product codes. So, we are going to replace them with empty strings every time.

Follow the steps below:

• Open the Find and Replace dialog box.
• In the Replace tab of the box, insert ™ in the Find what: field.
• Leave the Replace with: field blank as we want to replace the character with an empty string.
• Then click on Replace All.

• A message box will pop up indicating the number of replacements that are made. Click OK on it.

• Close the Find and Replace box after this is over.

Case 2: Replace a Character with Different Characters Each Time

Imagine a dataset like the following image. To clean the data and make the sentences or words meaningful, we need to replace the ™ characters. But all ™ characters are not replaceable with the same character. For example, Wow™ Look at it go– here ™ is supposed to be replaced by an exclamation mark (!). But in, It™s a bird– here ™ is to be replaced with an apostrophe (‘).

To replace special characters in such cases, you can use the Find and Replace tool in the following way.

Steps to Follow:

• Open up the Find and Replace dialog box.
• Select the first cell with the replaceable value (B6).
• Insert ™ in the Find what field.
• Insert ! in the Replace with field.
• Select Replace.

• The ™ in the first cell will change to !.

• The selection will automatically shift down. You can also manually change the selection at this point if you want the change to happen in another cell.
• Insert an apostrophe (’) in the Replace with field next and click on Replace.

• Repeat this for the rest of the cells and you can get different characters for each cell.

2. Use Flash Fill Feature When the Special Characters Maintain a Definite Pattern in Your Data

Excel Flash Fill feature can sense, repeat, and adjust different patterns in a range. If you establish a pattern in the first 2 cells in a range, the Flash Fill tool can follow this pattern and show autofill suggestions for the rest of the cells.

The downside is, that the Flash Fill can sense patterns only from a range adjacent to it and the changes must follow a pattern.

Now you will learn how to replace special characters in Excel using this Flash Fill feature.

Look at the dataset below. It has the special character, ™, at the start and end of every number.

To replace the ™ characters with Flash Fill, follow the steps below.

Steps to Follow:

• First, write the product code 4227 manually in cell D6, which is adjacent to the source cell C6.

• Again, start writing the next product code 4685 in cell D7. At this point, the Flash Fill tool will detect the pattern you are establishing and suggest the rest of the values like the image below.

• Press Enter once the suggestion appears.

Note:

You can use the keyboard shortcut, Ctrl+E to use the Flash Fill feature too. For example, you could insert 4227 as the first cell value and press Ctrl+E. It would give the exact same result.

Read More: Find And Replace Multiple Values in Excel

3. Use Excel Functions to Replace Special Characters in Excel in Different Cases

The main functions for replacing a character with another are SUBSTITUTE and REPLACE.

3.1 Use SUBSTITUTE Function Once to When the Replaceable Character Is Same in All Cells

The SUBSTITUTE function finds a character or group of sequential characters in a text and replaces every instance (by default) of the characters with a new text set inside the formula.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

To replace a special character, we need to insert the replaceable character in the second argument.

Let’s take the following dataset. All the invalid product codes have some unnecessary ™ characters

We are going to replace ™ with empty strings using this function like the following.

3.2 Use Nested SUBSTITUTE functions to Replace Multiple Special Characters

The SUBSTITUTE function can substitute one character at a time from a string. If there is more than one character you want to replace with the function we can nest multiple SUBSTITUTE functions.

Let’s take a dataset like below which contains three special characters: ™, ©, and ®.

We need to nest 3 SUBSTITUTE functions like this below.

Each of the SUBSTITUTE functions here replaces different special characters with empty strings.

Note:

This method is not suitable to work with when there are a lot of characters to replace.

Read More: How to Substitute Multiple Characters in Excel

3.3 Use REPLACE Function to Replace Special Characters Wholly or Partially from a Certain Position

The Excel REPLACE function replaces a part of a text string with a different text string. The syntax of the REPLACE function is as follows:

=REPLACE(old_text, start_num, num_chars, new_text)

Here, you have to specify the cell, i.e., old_text, where the replaceable text lies. Then input start_num, i.e., from which position in the string, the replacement will start. Then specify num_chars i.e., the number of characters to be replaced by new_text in the 4th argument.

To remove special characters from your dataset, you may use the REPLACE function too. In the following dataset, we have 2 ™ characters in each product code. We want to keep one ™ and replace the other one with an empty string.

We can use the formula like this for that:

This way, the formula is replacing the first character with an empty string.

4. Use a Formula That Can Replace Special Characters Based on Conditions

In this section, you will learn how to use a formula to replace characters based on conditions.

Say, you want to keep only the numbers in your product codes; the rest of the characters will be replaced.

The character codes of numbers in Excel are 48, 49, 50, 51, 52, 53, 54, 55, 56, and 57.

We can say that we will replace characters-

• Whose codes are less than 48 and
• Whose codes are greater than 57.

To do this, go to cell E6 and insert the following formula. Then copy it for the rest of the cells.

If you want to add more conditions, i.e., remove characters that are not numbers, but keep “#” characters, the formula will be

Here, CODE(MID(C6,SEQUENCE(LEN(C6)),1))=35 condition is used to keep the # characters (35 is the character code of #).

5. Use the LAMBDA Function to Replace a Set of Special Characters at Once

Excel’s built-in functions can only replace one special character at a time unless we use the nested SUBSTITUTE formula. That, too, is very impractical for a high number of characters. The LAMBDA function can allow us to create custom functions that can replace multiple characters with a single formula.

Here is a dataset like that.

The characters we want to remove are in cell B6. The procedure for creating this function is as follows.

• Go to the Formulas tab in the ribbon and select Name Manager.

• Select New in the Name Manager box.

• Another box will pop up called New Name. Select a name in the Name field. We named it ReplaceChars.
• In the Refers to field, use this formula:

• After that, close both the boxes.
• Now go back to the spreadsheet and use the formula below.

This way, the function will now remove all of the characters that are in the B6 cell.

6. Using VBA to Replace Special Characters

Besides LAMBDA, we can also create custom functions with VBA. Remember that these functions also work for predefined patterns in the code.

To use VBA to replace special characters in Excel, follow these steps.

• Select Developer tab (enable the Developer tab on your ribbon) > Visual Basic.

• In the VBA window, select Insert > Module.

• Insert the following code in the module.

Function ReplaceUnwantedChars(str As String, chars As String)
  For index = 1 To Len(chars)
    str = Replace(str, Mid(chars, index, 1), "#")
  Next
  ReplaceUnwantedChars = str
End Function

• Close the window and use the function in the spreadsheet like below now.

How to Replace Characters Which You Cannot Type in Excel?

There are some invisible characters or some you can’t copy in Excel. The special characters also fall under this category. We can use the CODE and CHAR functions in these cases to replace them.

For demonstration, let’s take a dataset with characters we can’t type.

We can find the character code of § using the following formula.

The result gives us 167. So, using CHAR(167) will return the character §.

We can use this in formulas to indicate the character.

For example, use the following formula now to remove the character from the product codes.

How to Replace Foreign Letters with English Alphabets in Excel?

With the help of VBA, we can replace foreign letters with English alphabets too. Most of the foreign characters are also treated as special characters in Office applications.

Let’s take a sample dataset with Spanish words that have the same spelling as English.

• Open a new module in the VBA window and insert the following code in it.

Function AccReplace(CellVal As String)
Dim Esp As String * 1
Dim Eng As String * 1
Dim i As Integer
Const EspChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const EngChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
For i = 1 To Len(EspChars)
Esp = Mid(EspChars, i, 1)
Eng = Mid(EngChars, i, 1)
CellVal = Replace(CellVal, Esp, Eng)
Next
AccReplace = CellVal
End Function

• Go back to the sheet and use the formula below.

Download Practice Workbook

This is how we can replace special characters in Excel using different features, functions, and VBA macro. With the Find and Replace feature, you can replace a single character with both a specific character and different characters. The Flash Fill feature replaces these special characters in a pattern quickly. We also showed how to use SUBSTITUTE and REPLACE functions to replace single or multiple special characters. In the fourth method, we showed a combination of functions to remove any non-numeric characters.

With LAMBDA and VBA, we can also create custom functions that can replace multiple special characters in one go.

Lastly, we have shown how we can deal with replacing characters that we can’t type and foreign characters.

If you know any other methods or face any problems that we have not discussed in this post, please share them with us in the comment box.

Related Articles

• How to Find and Replace Values Using Wildcards in Excel
• How to Find and Replace Asterisk (*) Character in Excel
• Find and Replace Tab Character in Excel
• [Fixed!] Excel Find and Replace Not Working
• How to Find and Replace in Excel Column

<< Go Back to Find and Replace | Learn Excel