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

 

Introduction

In real-world Excel usage, data is rarely clean. Data collected from websites, software exports, CSV files, or manual entry often contains unwanted special characters, extra spaces, mixed text cases, and blank rows. These issues make reports unreliable and dashboards inaccurate.

Manually cleaning such data every day is slow and frustrating. This is where Excel VBA automation becomes extremely useful.

In this tutorial, we will build and understand a smart Excel VBA Data Cleaning Tool that:

• Removes special characters from Name and City

• Keeps email format safe (@ . _)

• Leaves numeric values unchanged

• Deletes blank rows

• Works with one click

This guide is written especially for beginners, with clear logic and line-by-line explanation.

You just need to copy all the VBA codes and paste them into one module, one after another. Then run the VBA code; it will remove all the special characters from the dataset and clean it for you.

---

What Kind of Data Problems Are We Solving?

Let’s look at a common real-life problem.

Example of Dirty Data

Problems in This Data

• @ # % in Name

• @ # in City

• Multiple @ in Email

• Extra spaces

• Mixed text case

---

Our Data Cleaning Rules (Very Important)

We will apply different rules for different columns, just like real companies do.

Column	Rule
Name	Remove all special characters, Proper Case
City	Remove all special characters, Proper Case
Email	Keep @ . _, remove other junk
Amount	Do nothing (numbers stay safe)

This is called column-wise smart cleaning.

---

Final Output After Cleaning

Name	City	Email	Amount
John Doe	Delhi	jo@hn.doemail.com	5000

✔ Clean
✔ Professional
✔ Ready for reports

---

Step 1: Open VBA Editor

1. Open Excel

2. Press ALT + F11

3. Click Insert → Module

4. A new module (Module1) will open

👉 All code will be placed in ONE module only

---

Step 2: Main VBA Macro (Controller Code)

This macro controls the entire cleaning process.

What This Macro Does

• Loops through each row

• Sends Name & City for text cleaning

• Sends Email for safe email cleaning

• Leaves Amount untouched

• Deletes blank rows

---

✅ Main VBA Code

Sub Advanced_DataCleaning_Main() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ActiveSheet Application.ScreenUpdating = False Application.EnableEvents = False lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 1 Then GoTo ExitHandler For i = 1 To lastRow ' Column A – Name If ws.Cells(i, "A").Value <> "" Then ws.Cells(i, "A").Value = Clean_Name_City(ws.Cells(i, "A").Value) End If ' Column B – City If ws.Cells(i, "B").Value <> "" Then ws.Cells(i, "B").Value = Clean_Name_City(ws.Cells(i, "B").Value) End If ' Column C – Email If ws.Cells(i, "C").Value <> "" Then ws.Cells(i, "C").Value = Clean_Email(ws.Cells(i, "C").Value) End If ' Column D – Amount (NO CHANGE) Next i DeleteBlankRows ws MsgBox "Advanced Data Cleaning Completed Successfully!", vbInformation ExitHandler: Application.ScreenUpdating = True Application.EnableEvents = True End Sub

---

Step 3: How Name & City Special Characters Are Removed

This function removes everything except letters and spaces.

Logic Used

• Loop through each character

• Keep only A–Z, a–z, and space

• Remove symbols like @ # % $ & *

• Convert result to Proper Case

---

✅ Name & City Cleaning Function

Function Clean_Name_City(txt As String) As String Dim i As Long Dim result As String Dim ch As String txt = Application.WorksheetFunction.Trim(txt) For i = 1 To Len(txt) ch = Mid(txt, i, 1) If ch Like "[A-Za-z ]" Then result = result & ch End If Next i Clean_Name_City = Application.WorksheetFunction.Proper(result) End Function

---

Line-by-Line Explanation

• Trim(txt) → removes extra spaces

• For i = 1 To Len(txt) → checks each character

• Mid(txt, i, 1) → extracts one character

• [A-Za-z ] → allows only letters & space

• Special characters are ignored

• Proper() → converts text to proper case

---

Step 4: Safe Email Cleaning (Very Important)

Emails are special.
We must NOT remove:

• @

• .

• _

Logic Used

• Convert email to lowercase

• Allow only a–z, 0–9, @, ., _

• Remove everything else

---

✅ Email Cleaning Function

Function Clean_Email(txt As String) As String Dim i As Long Dim result As String Dim ch As String txt = LCase(Application.WorksheetFunction.Trim(txt)) For i = 1 To Len(txt) ch = Mid(txt, i, 1) If ch Like "[a-z0-9@._]" Then result = result & ch End If Next i Clean_Email = result End Function

---

Example Email Cleaning

Before:

jo@hn.doe@@mail.com

After:

jo@hn.doe@mail.com

✔ Email format preserved
✔ Junk removed

---

Step 5: Deleting Blank Rows

Blank rows cause issues in reports and dashboards.

What This Code Does

• Checks each row from bottom to top

• Deletes rows with no data

---

✅ Delete Blank Rows Code

Sub DeleteBlankRows(ws As Worksheet) Dim lastRow As Long Dim i As Long lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row For i = lastRow To 1 Step -1 If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then ws.Rows(i).Delete End If Next i End Sub

---

Step 6: How to Run the Tool

1. Press ALT + F8

2. Select Advanced_DataCleaning_Main

3. Click Run

🎉 Your data is cleaned automatically.

---

Why This VBA Project Is Industry-Style

✔ Column-wise logic
✔ Data-type aware cleaning
✔ Safe email handling
✔ No damage to numbers
✔ Beginner-friendly
✔ Reusable automation

This is exactly how real Excel automation projects are built.

---

Conclusion

Excel VBA Data Cleaning Automation is a must-learn skill for anyone working with Excel regularly. This project shows how to clean messy data intelligently, not blindly.

With one click, you can:

• Remove special characters correctly

• Keep important formats safe

• Prepare clean, professional datasets

This project is perfect for:

• Excel learners

• Office professionals

• Bloggers

• Automation portfolios

Related Article

XLOOKUP Function in Excel – Complete Guide with Examples (Beginner to Advanced)

Excel VLOOKUP Function in VBA: A Simple Guide for Beginners