When working with Excel, you may encounter data containing special characters such as #, @, !, %, $, etc. In the previous article, we learned about ways to find special characters in Excel. In this article, we will explore many methods to effectively remove special characters in Excel.
Nội dung
- Method 1: Use Power Query to Remove Special Characters in Excel
- Method 2: Use VBA (Macro) to Remove Special Characters in Excel
- Method 3: Use Find & Replace to Remove Special Characters in Excel
- Method 4: Use Excel Functions to Remove Special Characters
- Method 5: Use Flash Fill to Remove Special Characters in Excel
- Conclusion
Method 1: Use Power Query to Remove Special Characters in Excel
Power Query is a powerful Excel tool that allows you to clean and transform data without using complex formulas or VBA. With Power Query, you can easily find and remove special characters.
Step 1: Convert Data to a Table
- Select the data range containing special characters. (e.g., Column A if your data is in A1:A5).
- Go to Data tab → Click From Table/Range.
- In the Create Table dialog box, confirm the data range and click OK.
Your data will now open in the Power Query Editor.
Step 2: Add a Custom Column to Remove Special Characters
- In Power Query Editor, go to Add Column → Click Custom Column.
- Name the new column “Clean Code”.
- Enter the following formula in the Custom column formula box:
=Text.Select([[Library card number], {"A".."Z", "a".."z", "0".."9"})
✅ Explanation of the formula:
- Text.Select: Selects only the specified characters.
- [[Library card number]: The column containing the data to be cleaned.
- {“A”..”Z”, “a”..”z”, “0”..”9″}: Retains only uppercase letters, lowercase letters, and numbers, removing all special characters.
- Click OK to apply the formula.
Step 3: Load Cleaned Data Back to Excel
- Go to Home tab → Click Close & Load.
- Power Query will return the cleaned data as a new table in Excel.
Comparison of Methods
| ✅ Advantages | ❌ Disadvantages |
| – No formulas or VBA required. – Automates the cleaning process. – Works well with large datasets. | – Requires knowledge of Power Query. – Returns a new table instead of modifying the original data. |
Method 2: Use VBA (Macro) to Remove Special Characters in Excel
If you need to clean large datasets without manually entering formulas, VBA is an efficient solution.
Step 1: Open the VBA Editor
- Open Excel.
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
- In the menu, go to Insert → Click Module to create a new module.
Step 2: Write the VBA Code
Copy and paste the following VBA code into the module:
Function RemoveSpecialChars(ByVal inputStr As String) As String
Dim i As Integer
Dim result As String
Dim char As String
' Loop through each character in the string
For i = 1 To Len(inputStr)
char = Mid(inputStr, i, 1)
' Keep only letters (A-Z, a-z) and numbers (0-9)
If char Like "[A-Za-z0-9]" Then
result = result & char
End If
Next i
RemoveSpecialChars = result
End Function
✅ Explanation of the VBA Code:
- Function RemoveSpecialChars(ByVal inputStr As String) As String
→ Defines a function that takes an input string and returns a cleaned string. - For i = 1 To Len(inputStr)
→ Loops through each character in the input string. - If char Like “[A-Za-z0-9]” Then
→ Checks if the character is a letter or a number. - result = result & char
→ Adds valid characters to the cleaned result. - RemoveSpecialChars = result
→ Returns the cleaned string.
Step 3: Apply the VBA Function in Excel
- After entering the VBA code, close the VBA editor and return to Excel.
- If your data is in Column A, enter the following formula in B2:
=RemoveSpecialChars(A2) - Drag down the formula to apply it to all rows.
Comparison of Methods
| ✅ Advantages | ❌ Disadvantages |
| – Handles large datasets quickly. – Fully automated once set up. – Can be customized easily. | – Macros must be enabled. – Requires basic VBA knowledge. |
Method 3: Use Find & Replace to Remove Special Characters in Excel
Find & Replace is a built-in Excel feature that allows you to manually remove specific special characters.
Step 1: Open the Find & Replace Dialog Box
- Select the data range.
- Press Ctrl + H to open Find & Replace.
Step 2: Remove Special Characters
- In Find what, enter the special character you want to remove (e.g.,
@,#,!,%,&, etc.). - Leave Replace with blank.
- Click Replace All.
- Repeat the process for other special characters.
Comparison of Methods
| Method | ✅ Advantages | ❌ Disadvantages |
|---|---|---|
| Find & Replace | – Very simple, no coding required. – Works well for a small dataset. | – Manual process. – Must remove each special character separately. – Not suitable for large datasets. |
Method 4: Use Excel Functions to Remove Special Characters
Excel provides several built-in functions that can help remove unwanted characters from your data.
1. Use the SUBSTITUTE Function
The SUBSTITUTE function replaces specific characters in a text string. It is useful when you want to remove certain special characters manually.
Syntax:
SUBSTITUTE(text, old_text, new_text, [instance_num])
- text: The original text.
- old_text: The character or string to replace.
- new_text: The replacement string (use
""to remove the character). - instance_num (optional): Specifies which occurrence to replace (if omitted, all occurrences are replaced).
Formula for A2:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "@", ""), "$", ""), "%", "")
| Advantages | Disadvantages |
|---|---|
| Simple and easy to use. | Requires manual listing of each special character. |
| Works without enabling macros or Power Query. | Not efficient for large datasets with many different special characters. |
2. Use CLEAN and TRIM Functions
These functions help remove non-printable characters and extra spaces.
Formula:
=TRIM(CLEAN(A2))| Advantages | Disadvantages |
|---|---|
| Removes hidden non-printable characters. | Does not remove standard special characters like @, #, $, %. |
| Useful when working with imported or copied data. | Limited functionality compared to other methods. |
3. Use an Array Formula to Remove All Special Characters
This method automatically removes all non-alphanumeric characters without listing them manually.
Formula:
=TEXTJOIN("",TRUE,IF(ISNUMBER(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789")),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))
| Advantages | Disadvantages |
|---|---|
| Automatically removes all special characters. | More complex formula, harder to understand for beginners. |
| No need to list special characters manually. | In older Excel versions, requires pressing Ctrl + Shift + Enter. |
Method 5: Use Flash Fill to Remove Special Characters in Excel
Flash Fill is an automatic feature in Excel that detects patterns based on user input and fills in data accordingly. It does not require formulas, VBA, or Power Query—just an example input from the user.
This method is ideal when you need to clean up data quickly without using complex formulas.
Step 1: Manually Enter a Clean Data Sample
- Suppose you have a list of library card numbers containing special characters in Column A.
- In Cell B2, manually enter the cleaned version of A2 (without special characters).
Step 2: Activate Flash Fill
- In Cell B3, start typing the cleaned version of A3 (just the first few characters).
- Press Ctrl + E to let Excel automatically fill in the rest based on your pattern.
- If Excel does not detect the pattern, go to Data > Flash Fill.
Comparison of Methods
| Advantages | Disadvantages |
|---|---|
| No need for formulas, VBA, or Power Query. | Requires manual input for the first example. |
| Extremely easy to use—great for beginners. | May not work correctly with complex data patterns. |
| Fast processing for small datasets. | Does not update automatically if the source data changes. |
Conclusion
Removing special characters in Excel can be done in multiple ways, depending on your needs and skill level:
- Using Excel Functions: Quick and easy for small datasets, but difficult to handle when there are many different special characters.
- Using VBA (Macro): Flexible and customizable, but requires knowledge of VBA programming.
- Using Power Query: Efficient for large datasets, with a user-friendly interface—ideal for those who frequently work with large tables.
- Using Find & Replace: A simple, manual method that works well if you only need to remove specific characters.
- Using Add-ins (Kutools for Excel): Convenient and fast, requiring no formulas or VBA, but requires additional software installation.
Each method has its own pros and cons, so choose the one that best suits your situation. If you work with complex and frequent data cleaning tasks, VBA or Power Query is the best choice. If you only need simple processing, Excel functions or Find & Replace will suffice.
We hope this article helps you clean your data in Excel more effectively!
If you have any questions or want to discover more useful Excel tips, leave a comment and explore more on our page!
See more: Convert lowercase to uppercase in the entire Excel sheet