๐ Excel โ Level 5: Text Functions for Data Cleaning & Transformation
As a Data Analyst, you'll rarely receive perfectly clean data.
You may encounter:
" John"
"John "
"JOHN"
"john"
"John Smith"
"John Smith"
You may also have data such as:
EMP-001-IND
Mumbai, India
john.smith@email.com
+91-9876543210
Before analyzing this data, you often need to clean, extract, combine, split, or standardize text.
That's why Excel's text functions are extremely useful.
1๏ธโฃ TRIM()
What does it do?
TRIM() removes unnecessary spaces from text.
For example:
" John Smith "
becomes:
"John Smith"
Formula:
=TRIM(A2)Why is this important?
Suppose you have:
IT
IT
IT
IT
They may look identical, but hidden spaces can cause lookup and filtering problems.
For example:
=XLOOKUP("IT",A2:A100,B2:B100)may not behave as expected if the underlying values contain unwanted spaces.
Data Analyst use cases:
Use TRIM() for:
โข Customer names
โข Department names
โข Product names
โข Country names
โข Category values
2๏ธโฃ CLEAN()
CLEAN() removes many non-printing characters from text.
Formula:
=CLEAN(A2)This can be useful when data is copied from:
โข Websites
โข External systems
โข Reports
โข PDFs
โข Legacy applications
Sometimes invisible characters are present even though the text looks normal.
TRIM vs CLEAN:
TRIM() โ Removes unnecessary spaces.
CLEAN() โ Removes non-printing characters.
You can combine them:
=TRIM(CLEAN(A2))This is a very useful basic data-cleaning pattern.
3๏ธโฃ UPPER()
Converts text to uppercase.
=UPPER(A2)Example:
india
becomes:
INDIA
Why use it?
Suppose your dataset contains:
India
india
INDIA
You can standardize them using:
=UPPER(A2)Now they all become:
INDIA
4๏ธโฃ LOWER()
Converts text to lowercase.
=LOWER(A2)Example:
JOHN.SMITH@EMAIL.COM
becomes:
john.smith@email.com
This is particularly useful for standardizing:
โข Email addresses
โข Usernames
โข IDs
โข Text categories
โโโโโโโโโโ
5๏ธโฃ PROPER()
Converts text into proper case.
=PROPER(A2)Example:
john smith
becomes:
John Smith
And:
mumbai
becomes:
Mumbai
Important:
PROPER() is useful for presentation, but don't automatically use it for every dataset.
Some names, product codes, or abbreviations should remain uppercase.
For example:
IBM
SQL
USA
may become undesirable results if automatically converted to proper case.
6๏ธโฃ LEN()
LEN() returns the number of characters in a text string.
=LEN(A2)Example:
A2 = "John"
Result:
4
Why is this useful?
It can help identify:
โข Invalid IDs
โข Incorrect phone numbers
โข Unexpected text lengths
โข Data-quality issues
For example:
Employee IDs should always contain 6 characters.
You could check:
=IF(LEN(A2)=6,"Valid","Check")7๏ธโฃ LEFT()
LEFT() extracts characters from the beginning of a text string.
Syntax:
=LEFT(text,num_chars)Example:
EMP-001-IND
To extract the first three characters:
=LEFT(A2,3)Result:
EMP
8๏ธโฃ RIGHT()
RIGHT() extracts characters from the end of a text string.
Example:
EMP-001-IND
Formula:
=RIGHT(A2,3)Result:
IND
This can be useful for extracting:
โข Country codes
โข File extensions
โข Product suffixes
โข Transaction codes
9๏ธโฃ MID()