How to Remove Special Characters in Excel [7 Methods]

When working with data imported from different sources or generated through user input, we may encounter special characters that can complicate our analysis or presentation. Symbols, punctuation marks, non-printable characters, and other characters not found in the regular alphanumeric set are examples of special characters. Removing these special characters from the data is essential to ensure data consistency. In this article, I will show you 7 different techniques to remove special characters in Excel.

1. Using the Flash Fill Method to Remove Special Characters in Excel

Flash Fill is a powerful data manipulation tool in Microsoft Excel. It is the easiest way to remove the special characters in Excel. However, the Flash Fill feature is available only in Microsoft Excel 2013 and later versions.

Let’s say we have a Dataset with a few special characters, as seen in the image below. Let’s remove them by utilizing the Flash Fill feature.

To remove special characters in Excel, use the Flash Fill method. Here’s how:

1. Select a cell (D2) and type oH4OUmUs manually without typing the special characters.
2. Then select the next cell (D3).
3. Go to Data.
4. After that select Flash Fill from the Data Tools group. Or, click CTRL+E. 

Final Result

After applying Flash Fill we can see that the special characters from the Dataset have been removed.

2. Applying Find and Replace to Remove Special Characters in Excel

The Find and Replace command may come in handy to remove specific characters in some special circumstances. It works only on some specific types of datasets. In this type of dataset, we need the same set of special characters sequentially in every cell. The dataset is given below.

By applying the Find & Replace option, you can easily remove special characters in Excel. The process is given below:

1. First, choose cell (C2:C10) and press CTRL+C to copy the selected texts.
2. Then choose cell (D2:D10) and press CTRL+V to paste those selected texts.
3. After pasting, again choose cell (D2:D10) and press CTRL+F to open the Find and Replace dialog box.
4. In the Find and Replace dialog box, type “%#$” in the “Find What” box and leave the “Replace With” box blank.
5. Finish the process after clicking the “Replace All” button and a pop-up message will confirm all the replacements.

Final Result

After that, we can see that all unwanted special characters are removed.

Note: If we do not have sequentially special characters like “#%$” or if the special characters are placed randomly in every data, then we have to remove them one by one, using the Find and Replace tool multiple times.

3. Removing Special Characters with the SUBSTITUTE Function

Another way to remove the special character is by using a SUBSTITUTE function. It is used to replace a character with another.

Syntax

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(text,old_text,new_text),old_text,new_text),old_text,new_text)

Formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C9,"#",""),"!",""),"$",""),"%",""),"&",""),"%",""),"~","")

Formula Breakdown

• The inner SUBSTITUTE function takes the text from cell C9 and replaces all occurrences of “#” with an empty string. It effectively removes all pound symbols from the text in cell C9.

• The outer SUBSTITUTE function takes the result of the inner SUBSTITUTE function (i.e., the text with “#” removed) and further replaces all occurrences of”! ” with an empty string. It removes all exclamation marks from the text obtained after the inner replacement.

• This Nested SUBSTITUTE function is used to sequentially remove special characters from a text string, first removing “#” and then removing“! “. If there are more special characters we can use more substitute functions.

Excel’s SUBSTITUTE function makes it simple to eliminate special characters. The steps are listed below:

1. Copy the formula above.
2. Apply the formula in cell D2.
3. Drag the Fill Handle from D2 to D10.

Final Result 

As a result, all special characters from every cell have been removed.

4. Remove Special Characters in Excel Using Replace Function

Any character from any cell can be replaced using the REPLACE function. The methods below will show you how to replace special characters in Excel using the REPLACE function.

Syntax

= REPLACE(old_text, start_num, num_chars, new_text)

Formula

=REPLACE(C2,4,1,"")

Formula Breakdown

This formula in Excel is used to modify the contents of cell C2.

• It replaces a specified number of characters in the cell with a new string or blanks.
• In this specific case, starting from the fourth character in the cell content, one character is replaced with an empty string (“”).
• The parameters for the REPLACE function are defined as follows: the reference cell is C2, the starting position for replacement is the fourth character, the number of characters to replace is one, and the replacement string is an empty string.

This formula is often employed when you want to eliminate or replace specific characters within a text string in Excel.

If you want to remove special characters in Excel, you can use the REPLACE function. Here’s how:

1. Copy the formula above.
2. Apply the formula in cell D2.
3. Drag the Fill Handle from D2 to D10.

Final Result

As a result, all special characters from every cell have been removed by using REPLACE Function.

5. Remove Special Characters by Using Reduce Function

One of Excel 365’s flexible functions is REDUCE. It can be applied to conditional sum, count, maximum, and minimum calculations.

It is a LAMBDA helper function that applies a LAMBDA to each element of an array or range to convert it to an accumulated value.

Syntax:

=REDUCE([initial_value], array, lambda(accumulator, value))

Formula:

= REDUCE(C2:C10,Sp_Char[Character to Remove],LAMBDA(x,spchar,SUBSTITUTE(x,spchar,"")))

Formula Breakdown

• This formula utilizes the REDUCE function to iteratively apply a LAMBDA function to a range (C2:C10).
• The LAMBDA function takes two parameters, ‘x’ representing each cell value and ‘spchar’ representing a character specified in the named range ‘Sp_Char[Character to Remove]’.
• Within the LAMBDA, SUBSTITUTE is used to replace occurrences of the specified character with an empty string.
• The REDUCE function then accumulates these modified values, effectively removing the specified characters from the range.

This formula is particularly useful for bulk character removal within a range in Excel.

Excel’s REDUCE function can be used to remove special characters. Here’s how to do it:

1. Go to Insert.
2. Select Table or press CTRL+T to create “Table” for Character to Remove.
3. Insert values of the Table.
4. Rename the Table to Sp_char.
5. Copy the formula above.
6. Apply the formula in cell D2.

Final Result

Press the ENTER button and all special characters from the range D2:D10 will be removed by using the REDUCE Function.

6. Remove Special Characters in Excel Using Power Query

Power Query is a data transformation and cleaning tool in Excel that allows you to manipulate data from various sources. Power Query can be used to delete special characters from Excel if you are using Microsoft Excel 2016 or Excel 365. You can download it from the Microsoft website if you are using Microsoft Excel 2010 or 2013.

Custom Column Formula

=Text.Select([Password],{"A".."z","0".."9"})

Formula Breakdown

It is likely used in Power Query or Power BI.

• It selects and retains only alphanumeric characters from the ‘Password’ column.
• The expression within curly braces represents a range of characters, ensuring that both uppercase and lowercase letters (A to z) along with numeric digits (0 to 9) are included.

This helps filter and preserve only alphanumeric characters in the specified column, aiding data cleaning or transformation processes.

Here’s how you can remove special characters using Power Query:

1. Select C1:C10 with a header.
2. Go to Data and Select From Table/Range.
3. Then a Create Table message will appear and tick the My Table Has Header option.
4. After that click OK.
5. A new Window named Power Query Window will open. Then go to Add Column and select the Custom column in the Power Query Window.
6. Then the Custom Column will be opened and write “New Password” in the New Column Name option.
7. Paste the “Custom Column Formula” to the Custom Column Formula option and click the OK button.
8. Therefore click the Close & Load button from the File tab.

Final Result

After doing all these steps a new column will be generated automatically without special characters.

7. Applying VBA code to Remove Special Characters

Another approach is to use VBA code to remove special characters from Excel. VBA (Visual Basic for Applications) in Excel is a programming language that allows you to automate tasks, create custom functions, and build applications within Microsoft Excel.

To remove special characters from Excel, apply the VBA code:

1. Go to Developer and select Visual Basic or press ALT+F11.
2. Open a new window, select Insert, and create a Module.
3. Copy the code below and paste it.

   Sub RemoveSpecialCharacters()
   ' This subroutine removes special characters from a manually selected range in the active sheet.
   
   Dim targetRange As Range
   Dim cell As Range
   Dim cellValue As String
   Dim charIndex As Long
   Dim cleanedValue As String
   Dim specialChars As String
   Dim specialChar As String
   
   ' Define the special characters you want to remove
   specialChars = "!@#$%^&*()_+[]{}|;:'"",.<>?`~/-= "
   
   On Error Resume Next
   Set targetRange = Application.InputBox("Select a range to remove special characters:", Type:=8)
   On Error GoTo 0
   
   If targetRange Is Nothing Then
        MsgBox "No range selected. Operation canceled."
        Exit Sub
   End If
   
   Application.ScreenUpdating = False
   
   ' Loop through each cell in the selected range
   For Each cell In targetRange
        cellValue = cell.Value
        cleanedValue = ""
   
        ' Loop through each character in the cell value
        For charIndex = 1 To Len(cellValue)
            specialChar = Mid(cellValue, charIndex, 1)
   
            ' Check if the character is not in the list of special characters
            If InStr(1, specialChars, specialChar, vbBinaryCompare) = 0 Then
                cleanedValue = cleanedValue & specialChar
            End If
   
        Next charIndex
   
        ' Update the cell value with the cleaned value
        cell.Value = cleanedValue
   Next cell
   
   Application.ScreenUpdating = True
   End Sub

4. Save the Module by clicking the Save option or pressing CTRL+S.
5. Go to Developer and click the Macros button or you can easily open macro by pressing ALT+F8.
6. Click Run.
7. Select the Range.

Final Result

Clicking the run button automatically removes the special characters.

Conclusion

Various methods can accomplish the common data-cleaning task of removing special characters in Excel, including Excel functions, Power Query, and VBA.

In this article, I have discussed 7 methods of removing special characters in Excel. By following these methods, you can enhance your knowledge of Microsoft Excel.

Frequently Asked Questions (FAQs)

How do I find hidden special characters in Excel?

To find hidden special characters in Excel, you can use the CLEAN function in combination with the LEN function. Here’s how:

1. Choose a cell where you want to identify hidden special characters or create a new column for analysis.
2. Enter the following formula in the selected cell: =LEN(A1)-LEN(CLEAN(A1))
3. Adjust the cell reference (A1) based on the location of your target cell.
4. Press Enter to execute the formula. If the result is greater than zero, it indicates the presence of hidden special characters in the selected cell.
5. Copy the formula and apply it to other cells or columns as needed to identify hidden characters throughout your dataset.

This procedure utilizes the LEN and CLEAN functions to identify and count hidden special characters in Excel.

How to remove certain characters from a cell in Excel using formula?

To remove certain characters from a cell in Excel using a formula, you can use the SUBSTITUTE function. Here’s a featured snippet for the procedure:

1. Choose the cell from which you want to remove certain characters or create a new column if needed.
2. Enter the following formula in the selected cell: =SUBSTITUTE(A1, “character_to_remove”, “”)
3. Replace “A1” with the reference to the cell containing the text.
4. Replace “character_to_remove” with the specific character you want to remove.
5. Press Enter to apply the formula.
6. Copy the formula and apply it to other cells or columns to remove different characters.

This formula utilizes the SUBSTITUTE function to replace specified characters with an empty string, effectively removing them from the cell’s content.