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.

Rows, Columns & Cells Explained (Beginner-Friendly Guide)

 Microsoft Excel is built on a simple but powerful grid structure. Everything you do in Excel—entering data, applying formulas, creating charts, or cleaning datasets—depends on understanding rows, columns, and cells. If you master these three basics, Excel becomes far easier, faster, and less confusing.

This detailed guide explains rows, columns, and cells in clear language with practical examples, perfect for beginners and refresher learners.

What Is an Excel Worksheet?

An Excel worksheet looks like a large table made up of thousands of small boxes. These boxes are arranged in horizontal and vertical lines:

• Horizontal lines → Rows

• Vertical lines → Columns

• Each box → Cell

Together, rows and columns create the worksheet grid where data is stored and processed.

Understanding Rows in Excel

What Is a Row?

A row is a horizontal line of cells that runs from left to right across the worksheet.

How Rows Are Identified

• Rows are identified by numbers

• They start from 1 at the top

• Example: Row 1, Row 2, Row 3, and so on

Example

If you enter names of students like this:

Each name appears in a different row, one below the other.

Uses of Rows

Rows are commonly used to:

• Store individual records (one student, one product, one transaction)

• Insert or delete complete records

• Sort data vertically

• Apply formatting or formulas to full records

Important Row Facts

• A modern Excel worksheet has over 1 million rows

• You can hide or unhide rows

• Entire rows can be selected by clicking the row number

Understanding Columns in Excel

What Is a Column?

A column is a vertical line of cells that runs from top to bottom in the worksheet.

How Columns Are Identified

• Columns are identified by letters

• They start from A, B, C…

• After Z, they continue as AA, AB, AC…

Example

If you enter student details:

Here:

• “Name” is in Column A

• “Marks” is in Column B

Uses of Columns

Columns are used to:

• Store a specific type of data (names, dates, prices)

• Apply formulas to a full category

• Adjust width to fit data

• Filter data by category

Important Column Facts

• A worksheet has 16,384 columns

• Entire columns can be selected by clicking the column letter

• Column width can be increased or decreased

Understanding Cells in Excel

What Is a Cell?

A cell is the intersection of a row and a column.
It is the smallest unit in Excel where you enter data.

Cell Address (Cell Reference)

Each cell has a unique address made by combining:

• Column letter + Row number

Examples:

• A1 → Column A, Row 1

• B5 → Column B, Row 5

• C10 → Column C, Row 10

What Can a Cell Contain?

A cell can store:

• Text (Name, City)

• Numbers (Marks, Price)

• Dates and time

• Formulas (=A1+B1)

• Functions (=SUM(A1:A10))

Active Cell

The active cell is the currently selected cell:

• Highlighted with a bold border

• Its address is shown in the Name Box

What Is a Range in Excel?

Definition of Range

A range is a group of selected cells.

Range Examples

• A1:A5 → Vertical range

• A1:E1 → Horizontal range

• A1:C5 → Rectangular range

Why Ranges Are Important

Ranges are used for:

• Applying formulas

• Formatting data

• Creating charts

• Sorting and filtering

Difference Between Rows, Columns & Cells

Practical Example (Real-Life Use)

Imagine a sales report:

• Each row = one sale

• Each column = type of information

• Each cell = one data value

This structure makes data easy to calculate, analyze, and present.

Common Beginner Mistakes (And How to Avoid Them)

1. Confusing rows with columns

👉 Remember: Rows = numbers, Columns = letters

2.  Typing multiple values in one cell

👉 Keep one value per cell for clean data

3. Ignoring cell references in formulas

👉 Always double-check cell addresses

4. Not selecting full ranges

👉 Select entire required rows or columns when applying formulas

Tips to Work Faster with Rows, Columns & Cells

• Double-click column border to auto-fit width
• Use Ctrl + Arrow keys to jump across data
• Use Shift + Arrow keys to select ranges
• Click row/column headers to select entire rows or columns
• Name important ranges for easy reference

Why Rows, Columns & Cells Matter in Excel Automation

If you plan to learn:

• Excel formulas

• Conditional formatting

• Pivot tables

• VBA automation

• Power Query

Then understanding rows, columns, and cells is non-negotiable.
Every formula, macro, or automation task works by identifying and manipulating cells through rows and columns.

Final Words

Rows, columns, and cells form the foundation of Excel. Once you clearly understand how data is organized inside this grid, Excel becomes logical instead of confusing. Whether you are a student, office professional, or data learner, mastering these basics will boost your confidence and efficiency in Excel.

Related Post

Excel interface (Workbook, Worksheet, Ribbon)

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)

Excel interface (Workbook, Worksheet, Ribbon)

 

The Excel interface is your workspace, where you create, analyze, and present your data. Familiarizing yourself with the core parts of Excel will make your workflow faster and more efficient.

Key Components of the Excel Interface:

• Ribbon:

• The Ribbon is the toolbar at the top of the screen. It contains tabs (Home, Insert, Page Layout, Formulas, Data, Review, View, etc.), and each tab reveals different groups of commands.

• For example, the Home tab provides quick access to formatting, font, alignment, and clipboard functions.

• Quick Access Toolbar:

Located above or below the Ribbon, this customizable toolbar lets you pin your most-used commands (like Save, Undo, Redo) for easy access.

Workbook & Worksheets:

 An Excel file is called a Workbook. Inside it are Worksheets (tabs at the bottom left), which allow you to organize data into separate sheets within a single file.

• Name Box and Formula Bar:

• The Name Box (next to the formula bar) displays the cell reference or the name of a selected cell/range.

• The Formula Bar lets you view, enter, or edit data and formulas in the selected cell.

Cells, Rows, and Columns

• Each Worksheet is made up of rows (numbered) and columns (lettered).

• The intersection point forms a cell, identified by its column and row reference (like A1).

• Sheet Tabs:

• Status Bar:

• Located at the bottom of the Excel window, the status bar shows information like count, sum, or average of selected cells, and the current view or zoom level.

• Scroll Bars:

• Use the horizontal and vertical scroll bars to navigate through large sheets.

Customization Options:

You can personalize the Ribbon and Quick Access Toolbar to add or remove commands, making your interface serve your workflow needs

Navigation Tips:

• Use the arrow keys, mouse, or Ctrl+Arrow for quick movement.

• Right-click for context menus with shortcuts.

• The "Tell Me" feature (lightbulb icon) helps you quickly find tools or commands—just type what you need.

Summary:

Understanding the Excel interface is the foundation of working confidently and quickly in Excel. Spend time getting familiar with each part of the screen—you’ll save time and reduce errors as you move through your data and commands!

In the next lesson, we will learn about Rows, Columns & Cells explained

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)

What is Microsoft Excel & Where It Is Used (Beginner Guide)

 Microsoft Excel is one of the most popular and powerful tools used for data management, calculations, and analysis. Whether you are a student, office worker, business owner, or data professional, Excel plays an important role in daily work.

In this lesson, we will understand what Microsoft Excel is and where it is commonly used, in very simple language.

✅ What is Microsoft Excel?

Microsoft Excel is a spreadsheet software developed by Microsoft.
It is used to store, organize, calculate, and analyze data in the form of rows and columns.

An Excel file is called a Workbook, and inside a workbook there are Worksheets (Sheets).

---

🔹 Key Features of Microsoft Excel

• Data entry in rows and columns

• Automatic calculations using formulas

• Data formatting and charts

• Sorting and filtering data

---

🧩 Basic Structure of Excel

Excel works on a simple and easy structure:

• Rows → Horizontal (1, 2, 3…)

• Columns → Vertical (A, B, C…)

• Cell → Intersection of row and column (A1, B2)

• Range → Group of cells (A1:A10)

This structure makes data easy to read, understand, and manage.

Rows → Horizontal (1, 2, 3…)

--------------------------------------------------------------------------------------------

Columns → Vertical (A, B, C…)

------------------------------------------------------------------------------

Cell → Intersection of row and column (A3, C3)

---------------------------------------------------------------------------------------------------

Range → Group of cells (A1:A10)

-------------------------------------------------------------------------------------------------

This structure makes data easy to read and manage.

Where Is Microsoft Excel Used?

Microsoft Excel is used in almost every industry. Below are the most common areas:

---

1️⃣ Offices & Corporate Jobs

• Employee records

• Attendance sheets

• Salary calculations

• Daily reports

Excel is a basic requirement in most office jobs.

---

2️⃣ Accounting & Finance

• Budget planning

• Profit & loss statements

• Invoices and expenses

• Tax calculations

Excel formulas save time and reduce errors.

---

3️⃣ Business & Small Shops

• Sales tracking

• Stock / inventory management

• Customer data

• Monthly reports

Even small businesses use Excel daily.

---

4️⃣ Education (Students & Teachers)

• Marksheets

• Attendance records

• Project data

• Result analysis

Excel helps organize academic data easily.

---

5️⃣ Data Analysis & Reporting

• Large data handling

• Charts and dashboards

• Trend analysis

• Decision making

Excel is the first step toward data analytics.

---

6️⃣ Personal Use

• Monthly budget

• Expense tracking

• Loan calculation

• Personal planning

Excel is useful even for personal work at home..

Why Should You Learn Microsoft Excel?

Learning Excel gives you many benefits:

• ✔ Improves job opportunities

• ✔ Saves time using automation

• ✔ Helps in data understanding

• ✔ Useful in almost every career

Excel is a must-have skill in today’s job market.

Conclusion

Microsoft Excel is a powerful yet easy-to-learn tool used for data storage, calculation, and analysis. It is widely used in offices, businesses, education, finance, and personal life.

If you want to grow professionally, learning Excel is one of the best decisions you can make.

👉 In the next lesson, we will learn about Excel Interface & Ribbon.

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

 

In this blog post, we will learn how to find a cell value from a different worksheet using the Excel VLOOKUP function.

Introduction

When working in Excel, data is often stored in different sheets. For example:

• Employee details in one sheet

• Salary or department details in another

• Product list in one sheet and price list in another

In such cases, manually searching data is slow and error-prone.

This is where VLOOKUP becomes extremely useful.

In this beginner-friendly tutorial, you will learn:

• What VLOOKUP is

• How VLOOKUP works

• How to use VLOOKUP to fetch data from another sheet

• Common mistakes and how to fix them

What Is VLOOKUP in Excel?

VLOOKUP stands for Vertical Lookup.

It is used to:

Search for a value in the first column of a table and return a corresponding value from another column in the same row.

In simple words:
👉 “Find something in one sheet and bring related data from another sheet.”

VLOOKUP Syntax (Very Important)

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Explanation:

• lookup_value → Value you want to search

• table_array → Data range where Excel will search

• col_index_num → Column number to return data from

• range_lookup →

  • FALSE = Exact match (recommended for beginners)

  • TRUE = Approximate match

Example Scenario (Real-Life Example)

Sheet 1: Employee_List

Sheet 2: Salary_Data

🎯 Goal:
Bring Salary from Salary_Data sheet into Employee_List sheet using VLOOKUP.

Step-by-Step: VLOOKUP from Another Sheet

Step 1: Go to the Result Cell

In Employee_List, insert a new column named Salary.

Click on the first salary cell (for Emp ID 101).

Step 2: Write the VLOOKUP Formula

=VLOOKUP(A2, Salary_Data!A:C, 2, FALSE)

Step 3: Understand This Formula

• A2 → Emp ID you are searching

• Salary_Data!A:C → Lookup table from another sheet

• 2 → Salary column (2nd column in table)

• FALSE → Exact match

Press Enter ✔️

Step 4: Copy the Formula Down

Drag the formula down to fetch salary for all employees.

🎉 Done! Salary is automatically fetched from another sheet.

Fetching Another Column (Department)

To fetch Department, use:

=VLOOKUP(A2, Salary_Data!A:C, 3, FALSE)

Here:

• Column 3 = Department

Important Rules of VLOOKUP (Must Know)

-----------------------------------------------------------------------------------------------------

Rule 1: Lookup Column Must Be First

VLOOKUP always searches only in the first column of the selected range.

❌ Wrong:

RANGE (B:C) 

✅ Correct:

RANGE (A:C) 
-----------------------------------------------------------------------------------------------------

Rule 2: Exact Match Is Best for Beginners

Always use: False

This avoids wrong results.

----------------------------------------------------------------------------------------------------

Rule 3: Column Number Is Relative

Column numbers are counted inside the table range, not the sheet.

-------------------------------------------------------------------------------------------------

Common VLOOKUP Errors & Fixes

❌ Error 1: #N/A

Reason:
Lookup value not found.

Fix:

• Check spelling

• Remove extra spaces

• Use exact match (FALSE)

-----------------------------------------------------------------------------------------------

❌ Error 2: #REF!

Reason:
Wrong column number.

Fix:
Make sure col_index_num is within table range.

---------------------------------------------------------------------------------------------

❌ Error 3: Wrong Result

Reason:
Using TRUE instead of FALSE.

Fix:
Always use: False

Using IFERROR with VLOOKUP (Recommended)

=IFERROR(VLOOKUP(A2, Salary_Data!A:C, 2, FALSE), "Not Found")

This makes your report look clean and professional.

Why Use VLOOKUP from Another Sheet?

✔ Saves time
✔ Avoids manual searching
✔ Reduces errors
✔ Essential office skill
✔ Used in HR, Sales, Finance, Inventory

Real-World Use Cases

• Employee salary lookup

• Student marksheet automation

• Product price lookup

• GST rate lookup

• Inventory stock details

Limitations of VLOOKUP (Beginner Awareness)

• Cannot lookup right to left

• Breaks if columns move

• Slower for very large data

👉 These issues are solved using XLOOKUP or INDEX-MATCH (future topics).

Final Thoughts

VLOOKUP is one of the most important Excel functions for beginners.
Once you master VLOOKUP from another sheet, you unlock:

• Faster reporting

• Better automation

• Professional Excel skills

Related Article

Excel VLOOKUP Function in VBA: A Simple Guide for Beginners

XLOOKUP Function in Excel – Complete Guide with Examples