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.

Dataset of Flash Fill method

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.Flash fill manually data typing
  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+EApplying Flash Fill method to remove special characters

Final Result

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

Final result after using Flash Fill method

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.

Dataset for Find and Replace method

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.Find and replace copying process
  2. Then choose cell (D2:D10) and press CTRL+V to paste those selected texts.Find and replace pasting process
  3. After pasting, again choose cell (D2:D10) and press CTRL+F to open the Find and Replace dialog box.Find and replace finding and replacing process
  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.Find and replace window

Final Result

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

Find and replace method final result

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.Remove Special Character using Substitute function
  3. Drag the Fill Handle from D2 to D10.Substitute function drag down process

Final Result 

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

Final Result of Substitute Function

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.Replace Function Formula to remove special character
  3. Drag the Fill Handle from D2 to D10.dragging down Replace Function to remove special character in Excel

Final Result

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

Replace Function Final result to remove special character in Excel

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.Creating table for Reduce Function
  3. Insert values of the Table.Reduce function character to remove table
  4. Rename the Table to Sp_char.Reduce function table naming process
  5. Copy the formula above.
  6. Apply the formula in cell D2.Reduce function formula to remove special characters in Excel

Final Result

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

Final Result of reduce function to remove special characters in Excel

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.Power query table range selection
  3. Then a Create Table message will appear and tick the My Table Has Header option.
  4. After that click OK.Power query table naming
  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.Power query Add column
  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.Power query new column and formula
  8. Therefore click the Close & Load button from the File tab.Power query close & load process

Final Result

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

Final result for power query to remove special character in Excel

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.VBA opening for removing special character
  2. Open a new window, select Insert, and create a Module.VBA module creation
  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.VBA module saving
  5. Go to Developer and click the Macros button or you can easily open macro by pressing ALT+F8.VBA module macros
  6. Click Run.Running VBA module to remove special characters in Excel
  7. Select the Range.VBA module Range selection process

Final Result

Clicking the run button automatically removes the special characters.

VBA module Final result to remove special characters in Excel

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.

Rate this post

Leave a Reply

Your email address will not be published. Required fields are marked *