Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Text Functions & Data Cleaning in Excel

Learn how to clean inconsistent text, remove unwanted spaces, extract characters, standardize names, replace text, combine values, and prepare reliable datasets for Excel reports and PivotTables.

1. Introduction to Text Functions

Excel datasets often contain inconsistent text, extra spaces, unwanted characters, incorrectly formatted names, and different ways of writing the same category.

For example, the values Java, JAVA , and java may represent the same course, but inconsistent formatting can create problems when filtering or summarizing data.

Excel text functions help clean, extract, transform, and combine text so that data becomes easier to analyze.

Remember: Clean your source data before building reports. Consistent values help prevent unwanted duplicate categories in PivotTables.
Excel Data Cleaning Workflow
Raw Data Extra spaces · Mixed case · Unwanted characters Text Functions TRIM · CLEAN · SUBSTITUTE · PROPER · LEFT · RIGHT Clean and Consistent Data Accurate PivotTables and Reports

2. TRIM Function

The TRIM function removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to a single space.

Excel Formula
=TRIM(A2)
Example
Original Text Formula Result
Samadhan Patil =TRIM(A2) Samadhan Patil
Java Course =TRIM(A3) Java Course
SQL =TRIM(A4) SQL
Use TRIM when: Names, course names, or category values contain unnecessary ordinary spaces.

Important: TRIM does not remove every type of invisible whitespace. For example, nonbreaking spaces copied from websites may require additional cleanup.

3. CLEAN Function

The CLEAN function removes many nonprinting control characters from text. Such characters can sometimes appear when data is imported from external systems.

Excel Formula
=CLEAN(A2)
Example
Input Concept Formula Purpose
Text containing nonprinting characters =CLEAN(A2) Removes many nonprinting control characters.
Text with extra spaces and control characters =TRIM(CLEAN(A2)) Combines character cleanup and ordinary-space cleanup.
Remember: CLEAN is not a universal Unicode cleanup function. Some invisible characters, including nonbreaking spaces, may remain.

4. SUBSTITUTE Function

The SUBSTITUTE function replaces specified text with new text. It is useful for standardizing values and removing unwanted characters.

Formula Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
  • text: The original text or cell reference.
  • old_text: The text to replace.
  • new_text: The replacement text.
  • instance_num: Optional; specifies which occurrence to replace.
Examples
Original Value Formula Result
Java-Training =SUBSTITUTE(A2,"-"," ") Java Training
CIIT/Excel/2026 =SUBSTITUTE(A3,"/","-") CIIT-Excel-2026
101-101-101 =SUBSTITUTE(A4,"-","",2) 101101-101
Tip: SUBSTITUTE replaces matching text. The optional instance number lets you replace only a particular occurrence.

5. LEFT, RIGHT and MID Functions

These functions extract part of a text string. They are useful for separating codes, extracting prefixes, and retrieving characters from structured text.

Function Syntax Purpose
LEFT =LEFT(text, [num_chars]) Extracts characters from the beginning.
RIGHT =RIGHT(text, [num_chars]) Extracts characters from the end.
MID =MID(text, start_num, num_chars) Extracts characters from a specified position.
Example: Extracting a Student Code

Suppose cell A2 contains CIIT2026EXCEL.

LEFT Formula
=LEFT(A2,4)

Result: CIIT

RIGHT Formula
=RIGHT(A2,5)

Result: EXCEL

MID Formula
=MID(A2,5,4)

Result: 2026

How Text Extraction Works
Original Text: CIIT2026EXCEL C I I T 2 0 2 6 E X C E L LEFT(A2,4) CIIT First 4 characters MID(A2,5,4) 2026 Four characters from position 5 RIGHT(A2,5) EXCEL Last 5 characters Character positions start at 1.

6. PROPER, UPPER and LOWER Functions

These functions change the letter case of text. They are useful when names, course names, or labels have inconsistent capitalization.

Function Formula Result for "samIKSHA patil"
PROPER =PROPER(A2) Samiksha Patil
UPPER =UPPER(A2) SAMIKSHA PATIL
LOWER =LOWER(A2) samiksha patil
Note: PROPER capitalizes the first letter of words based on Excel's rules. Names containing special capitalization, acronyms, or specific brand styling may need manual correction.

7. LEN Function

The LEN function returns the number of characters in a text string, including spaces.

Excel Formula
=LEN(A2)
Examples
Text Formula Result
Java =LEN(A2) 4
SQL Course =LEN(A3) 10
CIIT2026 =LEN(A4) 8

LEN can help identify unexpected spaces or validate the length of codes. For example, a student code expected to contain eight characters can be checked with =LEN(A2)=8.

8. CONCAT Function

The CONCAT function combines text from multiple cells or text strings into one string.

Excel Formula
=CONCAT(A2," ",B2)
Example
First Name Last Name Formula Result
Samadhan Patil =CONCAT(A2," ",B2) Samadhan Patil
Samiksha Shinde =CONCAT(A3," ",B3) Samiksha Shinde
Omkar Jadhav =CONCAT(A4," ",B4) Omkar Jadhav
Important: CONCAT does not automatically insert spaces between values. Add " " in the formula wherever a space is required.

9. Combining Functions for Data Cleaning

In practical datasets, one function may not be enough. We can combine multiple text functions to clean and standardize values in a single formula.

Example 1: Clean Spaces and Capitalization
Excel Formula
=PROPER(TRIM(A2))

If A2 contains samadhan patil , the result is Samadhan Patil.

Example 2: Remove Control Characters and Spaces
Excel Formula
=TRIM(CLEAN(A2))

This formula removes many nonprinting control characters and cleans ordinary extra spaces.

Example 3: Standardize Course Labels
Excel Formula
=UPPER(TRIM(A2))

If A2 contains java , the result is JAVA. Consistent capitalization makes category values easier to compare.

Data-cleaning tip: Keep the original column until you have verified the cleaned results. After checking the output, you can use the cleaned values in a report or copy them as values into a new column.

10. Before and After Data Cleaning

Original Data Cleaning Formula Cleaned Data
java =UPPER(TRIM(A2)) JAVA
samiksha patil =PROPER(TRIM(A3)) Samiksha Patil
CIIT/2026 =SUBSTITUTE(A4,"/","-") CIIT-2026
CIIT2026EXCEL =MID(A5,5,4) 2026
Java =LEN(A6) 4

11. Common Mistakes to Avoid

Problem Solution
TRIM does not remove an invisible space Check for nonbreaking spaces and use suitable replacement or cleanup logic.
LEFT or RIGHT returns the wrong characters Verify the number of characters requested.
MID returns unexpected text Check the starting position and number of characters.
CONCAT joins names without spaces Include a space string, such as " ", in the formula.
PROPER changes a name incorrectly Review special capitalization and correct exceptions manually.
PivotTable still shows duplicate categories Standardize text, check hidden characters, and refresh the PivotTable.

12. Practice Tasks

Use a worksheet containing Student Name, Course, Student Code, and City. Complete the following tasks:

  1. Remove extra spaces from student names using TRIM.
  2. Remove nonprinting control characters using CLEAN.
  3. Convert course names to uppercase using UPPER.
  4. Convert names to proper case using PROPER.
  5. Extract the first four characters of a student code using LEFT.
  6. Extract the last four characters of a student code using RIGHT.
  7. Extract a year from a structured code using MID.
  8. Replace hyphens with spaces using SUBSTITUTE.
  9. Count the characters in each course name using LEN.
  10. Combine first and last names using CONCAT.
  11. Create a PivotTable using the cleaned Course column.

13. Important Points

  • TRIM removes ordinary extra spaces.
  • CLEAN removes many nonprinting control characters.
  • SUBSTITUTE replaces specified text with new text.
  • LEFT, RIGHT, and MID extract characters from text.
  • PROPER, UPPER, and LOWER change letter case.
  • LEN counts characters, including spaces.
  • CONCAT combines text values and does not automatically insert spaces.
  • Always validate cleaned values before using them in PivotTables or reports.

Summary

Excel text functions help clean inconsistent data, extract important characters, standardize capitalization, replace unwanted text, count characters, and combine values.

Functions such as TRIM, CLEAN, SUBSTITUTE, LEFT, RIGHT, MID, PROPER, UPPER, LOWER, LEN, and CONCAT are useful for preparing reliable data before analysis. Combining these functions can make data cleaning more efficient and improve the quality of Excel reports and PivotTables.