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.
Excel Data Cleaning Workflow
2. TRIM Function
The TRIM function removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to a single space.
=TRIM(A2)
Example
| Original Text | Formula | Result |
|---|---|---|
Samadhan Patil |
=TRIM(A2) |
Samadhan Patil |
Java Course |
=TRIM(A3) |
Java Course |
SQL |
=TRIM(A4) |
SQL |
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.
=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. |
4. SUBSTITUTE Function
The SUBSTITUTE function replaces specified text with new text. It is useful for standardizing values and removing unwanted characters.
=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 |
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(A2,4)
Result: CIIT
=RIGHT(A2,5)
Result: EXCEL
=MID(A2,5,4)
Result: 2026
How Text Extraction Works
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 |
7. LEN Function
The LEN function returns the number of characters in a text string, including spaces.
=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.
=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 |
" " 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
=PROPER(TRIM(A2))
If A2 contains samadhan patil , the result is
Samadhan Patil.
Example 2: Remove Control Characters and Spaces
=TRIM(CLEAN(A2))
This formula removes many nonprinting control characters and cleans ordinary extra spaces.
Example 3: Standardize Course Labels
=UPPER(TRIM(A2))
If A2 contains java , the result is
JAVA. Consistent capitalization makes category
values easier to compare.
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:
- Remove extra spaces from student names using TRIM.
- Remove nonprinting control characters using CLEAN.
- Convert course names to uppercase using UPPER.
- Convert names to proper case using PROPER.
- Extract the first four characters of a student code using LEFT.
- Extract the last four characters of a student code using RIGHT.
- Extract a year from a structured code using MID.
- Replace hyphens with spaces using SUBSTITUTE.
- Count the characters in each course name using LEN.
- Combine first and last names using CONCAT.
- 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.