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.

Excel replace special characters

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.


  • We have used Excel for Microsoft 365 in this tutorial. The LAMBDA function is exclusive to this version only.
  • The Flash Fill feature has been available since Excel 2013.
  • You can use the rest of the methods in other Excel versions too.

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.


How to Open Find and Replace Dialog Window?

We can open the Find and Replace dialog box in 2 ways.

  1. Go to Home ⇒ Editing ⇒ Find & Select ⇒ Replace.

selecting Find and Replace from ribbon

  1. Or, just press Ctrl+H to directly open up the Find and Replace dialog box.

Find and Replace box in Excel

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.


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.

Replacing with same special character using Find and Replace

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.

Replacing special character with empty string using Find and Replace

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

Find and Replace successful alert

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

All special characters replaced using Find and Replace

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 (‘).

Dataset that needs replacing with different characters

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.

Using Find and Replace to replace character from a single cell

  • The in the first cell will change to !.

Characters changed in a single cell with Find and Replace feature

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

Replacing with second character for second entry using Find and Replace

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

Replaced with different characters each time using Find and Replace

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.

Dataset with a pattern for Flash Fill

Steps to Follow:

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

Filling out first cell manually

  • 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.
Note: Excel may need a few more manual inputs depending on the length and complexity of the pattern. Keep inserting the values manually until the suggestion appears in these cases.

Flash Fill suggestion while filling out manually

  • Press Enter once the suggestion appears.

All special characters replaced with Flash Fill


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])

SUBSTITUTION function syntax in Excel

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

Dataset with repeated special character

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


Replacing special character using SUBSTITUTE function

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

Dataset with multiple special characters

We need to nest 3 SUBSTITUTE functions like this below.


Replacing multiple special characters with nested SUBSTITUTE function

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


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.

REPLACE function syntax in Excel

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.

Repeated same special character dataset

We can use the formula like this for that:


Using REPLACE function to replace special characters

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.

Multiple special characters with numbers dataset

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.


Formula to replace any non numeric characters

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 #).

Formula to replace non numeric characters except #

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.

LAMBDA function dataset

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.

Selecting Name Manager from ribbon

  • Select New in the Name Manager box.

Creating new name in the Name Manager dialog 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:

=LAMBDA(string,chars, IF(chars<>"", ReplaceChars(SUBSTITUTE(string, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), string))

Defining new function with LAMBDA

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


Using custom function created with LAMBDA

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.

Selecting Visual Basic from ribbon

  • In the VBA window, select Insert > Module.

Inserting new Module in Visual Basic window

  • 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), "#")
  ReplaceUnwantedChars = str
End Function
  • Close the window and use the function in the spreadsheet like below now.


Custom function created with VBA to replace special characters

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.

nontypable characters example

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


Finding out code number of the special character

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.


Replacing the special character with the help of character code

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.

Spanish words identical to 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)
AccReplace = CellVal
End Function
  • Go back to the sheet and use the formula below.


Accent replacement with custom function

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

<< Go Back to Find and Replace | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF