How to Remove Special Characters in Excel (Formula + VBA Method)

 

When working with real-world data in Microsoft Excel, you will often face a common problem — special characters in your data. These unwanted symbols can break formulas, cause errors in reports, and create issues while importing or exporting data.

In this detailed tutorial, you will learn multiple ways to remove special characters in Excel, using both Excel formulas and VBA automation. This guide is perfect for beginners, office users, and Excel learners who want clean and professional data.

What Are Special Characters in Excel?

Special characters are symbols that are not letters (A–Z), numbers (0–9), or spaces.

Common Examples:

@  #  $  %  &  *  !  ?  /  \  ( )  _  -  +  =

Example Dirty Data:

Such characters often appear due to:

• Data imported from websites
• Copy-paste from PDFs
• CRM or ERP exports
• User input mistakes

Why Should You Remove Special Characters?

Removing special characters helps you to:

• Clean and standardize data
• Avoid formula errors
• Improve data accuracy
• Prepare data for reports and dashboards
• Import data into databases or software

Clean data = Better results 📊

Method 1: Remove Special Characters Using Excel Formulas

Excel formulas are best when:

• You have small to medium datasets
• You don’t want to use VBA
• You want quick results

Method 1.1: Using SUBSTITUTE Function (Manual Way)

The SUBSTITUTE function replaces specific characters with blank values.

Syntax:

=SUBSTITUTE(text, old_text, new_text)

Example:

If cell A2 contains:

Amit@123

Formula:

=SUBSTITUTE(A2,"@","")

This removes only @.

⚠️ Limitation:

You must write one SUBSTITUTE for each character.

Removing Multiple Special Characters with Nested SUBSTITUTE

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"@",""),"#",""),"$","")

❌ Not practical for many characters
❌ Hard to maintain

Method 1.2: Using TEXTJOIN + MID + CODE (Advanced Formula)

This formula removes all non-alphanumeric characters.

=TEXTJOIN("",TRUE,IF((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=48)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))=122),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

✔ Removes almost all special characters

✔ No VBA required

⚠️ Works best in Excel 365 / Excel 2021
⚠️ Complex for beginners

Method 2: Remove Special Characters Using Excel VBA (Best Method)

If you work with large datasets or want a one-click solution, VBA is the best choice.

Benefits of VBA Method:

• Removes ALL special characters automatically
• Fast and efficient
• Reusable
• Ideal for automation projects

Step 1: Sample Dataset

Paste this data in Column A:

Step 2: Open VBA Editor

1. Press ALT + F11
2. Click Insert → Module
3. Paste the VBA code below

Step 3: VBA Code to Remove Special Characters

Sub CleanText_ProperCase()     Dim cell As Range     Dim cleanText As String     Dim i As Long     Dim ch As String     For Each cell In Selection                 cleanText = ""                 ' Step 1: Replace special characters with space         For i = 1 To Len(cell.Value)             ch = Mid(cell.Value, i, 1)                         If ch Like "[A-Za-z0-9 ]" Then                 cleanText = cleanText & ch             Else                 cleanText = cleanText & " "             End If         Next i                 ' Step 2: Remove extra spaces         cleanText = Application.WorksheetFunction.Trim(cleanText)                 ' Step 3: Convert to Proper Case         cell.Value = Application.WorksheetFunction.Proper(cleanText)     Next cell End Sub

Step 4: How to Run the VBA Code

1. Select the cells containing data

2. Press ALT + F8

3. Select RemoveSpecialCharacters

4. Click Run

✨ Done! All special characters will be removed instantly.

How This VBA Code Works (Line-by-Line Explanation)

• Loops through each selected cell

• Reads each character one by one

• Keeps only:

  • A–Z

  • a–z

  • Numbers

  • Spaces

• Removes symbols like @#$%^&*

• Trims extra spaces

This makes your data 100% clean and usable.

Bonus: Remove Only Specific Characters (Custom VBA)

If you want to remove only selected symbols, use this version:

Sub RemoveSpecificCharacters()     Dim cell As Range     For Each cell In Selection         cell.Value = Replace(cell.Value, "@", "")         cell.Value = Replace(cell.Value, "#", "")         cell.Value = Replace(cell.Value, "$", "")         cell.Value = Replace(cell.Value, "%", "")         cell.Value = Replace(cell.Value, "&", "")     Next cell End Sub

✔ Useful when rules are fixed
✔ Easy to modify

👉 Recommendation:

Use Formulas for small tasks and VBA for professional or repeated work.

Common Mistakes to Avoid

• Not keeping a backup of original data
• Running VBA on wrong cells
• Forgetting to select data before running macro
• Using complex formulas unnecessarily

Practical Use Cases

• Cleaning customer names
• Preparing data for Power BI
• Removing symbols from phone numbers
• Cleaning Excel data before upload
• Excel automation projects

Final Thoughts

Removing special characters is one of the most important Excel data-cleaning tasks. Whether you are a beginner or an advanced Excel user, knowing both formula-based and VBA-based methods gives you complete control over your data.

If you regularly work with Excel, learning VBA automation will save you hours of manual work and make you more productive.

Related Post

VLOOKUP for Beginners: Find Data from Another Sheet (Step-by-Step Guide)

Excel VBA Data Cleaning Automation – Remove Special Characters Step by Step (Beginner Guide)

❓ Frequently Asked Questions (FAQ)

Q1. How do I remove special characters in Excel without VBA?
You can use Excel formulas like SUBSTITUTE or advanced TEXTJOIN formulas to remove unwanted symbols.

Q2. What is the best way to remove special characters in Excel?
For large datasets, VBA is the best and fastest method as it automates the entire process.

Q3. Does removing special characters affect spaces between words?
If you replace special characters with spaces and apply TRIM, word separation remains intact.

Q4. Can VBA also capitalize the first letter automatically?
Yes, using the PROPER function in VBA converts text to Proper Case.