Text Functions and Data Cleaning with Practical Examples

Business data imported from websites, accounting software, CRM systems, CSV files and online forms is rarely ready for analysis. It may contain extra spaces, inconsistent capitalization, combined values, hidden characters, incorrect codes or duplicate information.

In this chapter, you will learn how to clean and restructure text using LEFT, RIGHT, MID, LEN, FIND, SEARCH, TRIM, CLEAN, UPPER, LOWER, PROPER, SUBSTITUTE, REPLACE, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, CONCAT and TEXTJOIN.

Learning Objectives

After completing this chapter, you should be able to:

• Extract selected characters from text.
• Calculate the length of a text value.
• Locate characters and words inside text.
• Remove extra spaces and hidden characters.
• Standardize the capitalization of names and codes.
• Replace selected text or character positions.
• Separate names, email addresses and product codes.
• Combine information from multiple cells.
• Split delimited text into separate rows or columns.
• Create a professional data-cleaning workflow.

1. Why Data Cleaning Is Important

Incorrect or inconsistent text can cause serious reporting problems. Two values that look similar to a user may be treated as different values by Excel.

Common Data-Quality Problems

• Extra spaces before or after a name
• Multiple spaces between words
• Names written in inconsistent capitalization
• Numbers stored as text
• Product information stored in one combined column
• Hidden line breaks and nonprinting characters
• Different spellings for the same category
• Phone numbers containing inconsistent symbols
• Email addresses containing unnecessary spaces
• Codes containing unwanted prefixes or suffixes

Example of Inconsistent Data

  rahul   sharma
PRIYA SINGH
imran khan  
NeHa DaS

These values should be standardized before they are used in PivotTables, lookups, filters or dashboards.

2. Create the Messy Customer Dataset

Create a worksheet named Raw_Customer_Data and enter the following records:

CustomerID	FullName	Email	Phone	ProductCode	Location
CUST-1001	rahul  sharma	RAHUL.SHARMA @GMAIL.COM	+91-98765-43210	LAP-EAST-2026	Kolkata,West Bengal,India
CUST-1002	PRIYA SINGH	PRIYA.SINGH@YAHOO.COM	98765 12345	MON-NORTH-2026	Delhi,Delhi,India
CUST-1003	imran khan	imran.khan@outlook.com	+91 91234 56789	KEY-WEST-2025	Mumbai,Maharashtra,India
CUST-1004	NeHa  DaS	neha.das @gmail.com	90000-11111	PRI-SOUTH-2026	Chennai,Tamil Nadu,India
CUST-1005	AMIT KUMAR	amit.kumar@gmail.com	(+91) 88888 22222	MOU-EAST-2025	Ranchi,Jharkhand,India

Convert the range into an Excel Table using Ctrl + T and name it CustomerData.

3. Using the LEN Function

LEN returns the number of characters in a text value. It counts letters, numbers, punctuation marks and spaces.

LEN Syntax

=LEN(text)

Count the Characters in a Customer ID

=LEN(A2)

Structured Reference Formula

=LEN([@CustomerID])

Detect Incorrect Customer IDs

If every Customer ID should contain nine characters, use:

=IF(
LEN([@CustomerID])=9,
"Valid",
"Check Customer ID"
)

Compare Length Before and After Cleaning

=LEN(B2)-LEN(TRIM(B2))

A result greater than zero indicates that unnecessary standard spaces were removed.

4. Using the LEFT Function

LEFT returns a specified number of characters from the beginning of a text value.

LEFT Syntax

=LEFT(text,[num_chars])

Extract the Customer Prefix

=LEFT(A2,4)

For CUST-1001, the result is CUST.

Extract the Product Abbreviation

=LEFT(E2,3)

For LAP-EAST-2026, the result is LAP.

Structured Reference Formula

=LEFT([@ProductCode],3)

5. Using the RIGHT Function

RIGHT returns a specified number of characters from the end of a text value.

RIGHT Syntax

=RIGHT(text,[num_chars])

Extract the Year from a Product Code

=RIGHT(E2,4)

For LAP-EAST-2026, the result is 2026.

Extract the Last Four Digits of a Phone Number

=RIGHT(D2,4)

This method works properly only after the phone number has been cleaned into a consistent format.

6. Using the MID Function

MID extracts characters from the middle of a text value.

MID Syntax

=MID(text,start_num,num_chars)

Extract the Numeric Part of Customer ID

=MID(A2,6,4)

For CUST-1001, the result is 1001.

Extract a Fixed Region Code

If the product code format is always ABC-EAST-2026, use:

=MID(E2,5,4)

This fixed-position method is not suitable when region names have different lengths. TEXTBEFORE and TEXTAFTER offer a more flexible solution.

7. LEFT, RIGHT and MID Comparison

Function	Extraction position	Example
LEFT	Beginning	Extract CUST from CUST-1001
RIGHT	End	Extract 2026 from LAP-EAST-2026
MID	Middle	Extract 1001 from CUST-1001

8. Using the FIND Function

FIND returns the position of one text value inside another. It is case-sensitive.

FIND Syntax

=FIND(find_text,within_text,[start_num])

Find the Position of the Hyphen

=FIND("-",A2)

Find the Position of @ in an Email Address

=FIND("@",C2)

Extract Text Before the First Hyphen

=LEFT(
E2,
FIND("-",E2)-1
)

Extract the Email Username

=LEFT(
C2,
FIND("@",C2)-1
)

9. Using the SEARCH Function

SEARCH works like FIND, but it is not case-sensitive and supports wildcard characters.

SEARCH Syntax

=SEARCH(find_text,within_text,[start_num])

Search for Gmail in an Email Address

=SEARCH("gmail",C2)

The formula can find gmail, Gmail or GMAIL.

Check Whether an Email Uses Gmail

=IF(
ISNUMBER(SEARCH("gmail",C2)),
"Gmail Account",
"Other Provider"
)

FIND vs SEARCH

FIND	SEARCH
Case-sensitive	Not case-sensitive
Does not support wildcards	Supports wildcards
Useful for exact character matching	Useful for flexible text searches

10. Using the TRIM Function

TRIM removes standard leading and trailing spaces and reduces multiple internal spaces to one.

TRIM Syntax

=TRIM(text)

Clean a Customer Name

=TRIM(B2)

Structured Reference Formula

=TRIM([@FullName])

Before and After TRIM

Before: "  rahul   sharma  "
After:  "rahul sharma"

Important Limitation

TRIM removes standard character-32 spaces. Data copied from websites may contain nonbreaking spaces represented by character 160. These require SUBSTITUTE.

11. Removing Nonbreaking Spaces

Replace Character 160 with a Standard Space

=SUBSTITUTE(B2,CHAR(160)," ")

Replace Nonbreaking Spaces and Apply TRIM

=TRIM(
SUBSTITUTE(
B2,
CHAR(160),
" "
)
)

This is a more reliable formula for names imported from websites or HTML pages.

12. Using the CLEAN Function

CLEAN removes many nonprinting characters from text. Such characters are commonly introduced during imports from external systems.

CLEAN Syntax

=CLEAN(text)

Clean Imported Text

=CLEAN(B2)

Combine CLEAN and TRIM

=TRIM(CLEAN(B2))

More Reliable Cleaning Formula

=TRIM(
CLEAN(
SUBSTITUTE(
B2,
CHAR(160),
" "
)
)
)

13. Removing Line Breaks

A cell may contain line breaks created using Alt + Enter. Use SUBSTITUTE to replace them.

Replace Line Breaks with a Space

=SUBSTITUTE(
A2,
CHAR(10),
" "
)

Remove Carriage Returns

=SUBSTITUTE(
A2,
CHAR(13),
""
)

Remove Both Types of Breaks

=TRIM(
SUBSTITUTE(
SUBSTITUTE(
A2,
CHAR(10),
" "
),
CHAR(13),
""
)
)

14. Using UPPER, LOWER and PROPER

Convert Text to Uppercase

=UPPER(B2)

Example result: RAHUL SHARMA.

Convert Text to Lowercase

=LOWER(C2)

LOWER is useful for standardizing email addresses.

Capitalize the First Letter of Every Word

=PROPER(B2)

Example result: Rahul Sharma.

Clean and Format a Customer Name

=PROPER(
TRIM(
CLEAN(
SUBSTITUTE(
B2,
CHAR(160),
" "
)
)
)
)

Clean and Format an Email Address

=LOWER(
SUBSTITUTE(
TRIM(C2),
" ",
""
)
)

15. Important Limitation of PROPER

PROPER may not format every real name or abbreviation correctly.

Input:  MCDONALD
Result: Mcdonald

Input:  ABC PVT LTD
Result: Abc Pvt Ltd

Review names, abbreviations and organization names after applying PROPER.

16. Using the EXACT Function

EXACT compares two text values and returns TRUE only when they match exactly, including uppercase and lowercase characters.

EXACT Syntax

=EXACT(text1,text2)

Example

=EXACT(A2,B2)

Case-Sensitive Code Validation

=IF(
EXACT(A2,UPPER(A2)),
"Valid Uppercase Code",
"Check Code"
)

17. Using the SUBSTITUTE Function

SUBSTITUTE replaces selected text wherever it appears inside a value.

SUBSTITUTE Syntax

=SUBSTITUTE(text,old_text,new_text,[instance_num])

Remove Hyphens from a Phone Number

=SUBSTITUTE(D2,"-","")

Remove Spaces

=SUBSTITUTE(D2," ","")

Remove Parentheses

=SUBSTITUTE(
SUBSTITUTE(
D2,
"(",
""
),
")",
""
)

Clean Multiple Phone-Number Symbols

=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
D2,
"+91",
""
),
"-",
""
),
" ",
""
),
"(",
""
)

Add another SUBSTITUTE if the closing parenthesis also needs to be removed.

Replace Only the Second Occurrence

=SUBSTITUTE(A2,"-","/",2)

The fourth argument specifies which occurrence should be replaced.

18. Using the REPLACE Function

REPLACE changes text based on character position rather than matching a specific word.

REPLACE Syntax

=REPLACE(old_text,start_num,num_chars,new_text)

Mask the First Six Digits of a Phone Number

=REPLACE(D2,1,6,"XXXXXX")

Replace a Product-Code Prefix

=REPLACE(E2,1,3,"NEW")

SUBSTITUTE vs REPLACE

SUBSTITUTE	REPLACE
Replaces matching text	Replaces characters by position
Suitable when old text is known	Suitable when the position is known
Can replace one or every occurrence	Replaces a specified number of characters

19. Using TEXTBEFORE

TEXTBEFORE returns text appearing before a selected delimiter.

TEXTBEFORE Syntax

=TEXTBEFORE(text,delimiter,[instance_num])

Extract Product Abbreviation

=TEXTBEFORE(E2,"-")

For LAP-EAST-2026, the result is LAP.

Extract Email Username

=TEXTBEFORE(C2,"@")

Extract City from Location

=TEXTBEFORE(F2,",")

Extract Everything Before the Second Hyphen

=TEXTBEFORE(E2,"-",2)

For LAP-EAST-2026, the result is LAP-EAST.

20. Using TEXTAFTER

TEXTAFTER returns text appearing after a selected delimiter.

TEXTAFTER Syntax

=TEXTAFTER(text,delimiter,[instance_num])

Extract Email Domain

=TEXTAFTER(C2,"@")

Extract Everything After the First Hyphen

=TEXTAFTER(E2,"-")

Extract Year After the Second Hyphen

=TEXTAFTER(E2,"-",2)

Extract Country from Location

=TEXTAFTER(F2,",",-1)

Using -1 searches from the end and returns text after the last comma.

21. Extract First and Last Names

Suppose a cleaned full name is stored in B2.

Extract First Name

=TEXTBEFORE(B2," ")

Extract Last Name

=TEXTAFTER(B2," ",-1)

Handle a Single-Word Name

=IFERROR(
TEXTBEFORE(B2," "),
B2
)

Extract the Last Name Safely

=IFERROR(
TEXTAFTER(B2," ",-1),
""
)

22. Using the TEXTSPLIT Function

TEXTSPLIT separates text into multiple columns or rows using selected delimiters.

TEXTSPLIT Syntax

=TEXTSPLIT(
text,
col_delimiter,
[row_delimiter],
[ignore_empty],
[match_mode],
[pad_with]
)

Split Product Code into Columns

=TEXTSPLIT(E2,"-")

The formula returns three separate values:

LAP | EAST | 2026

Split Location into Columns

=TEXTSPLIT(F2,",")

The formula returns City, State and Country in separate columns.

Split a List into Rows

Suppose A2 contains Laptop,Monitor,Printer.

=TEXTSPLIT(A2,,",")

Because the comma is supplied as the row delimiter, each item appears in a separate row.

Ignore Empty Values

=TEXTSPLIT(A2,",",,TRUE)

23. Using CONCAT

CONCAT combines text from multiple cells or ranges.

CONCAT Syntax

=CONCAT(text1,[text2],...)

Combine First Name and Last Name

=CONCAT(A2," ",B2)

Create an Order Reference

=CONCAT(
A2,
"-",
TEXT(B2,"yyyymmdd")
)

Combine a Complete Range

=CONCAT(A2:C2)

CONCAT does not automatically insert a separator between values.

24. Using TEXTJOIN

TEXTJOIN combines multiple values using a selected delimiter. It can also ignore blank cells.

TEXTJOIN Syntax

=TEXTJOIN(delimiter,ignore_empty,text1,[text2],...)

Combine Address Fields

=TEXTJOIN(
", ",
TRUE,
A2,
B2,
C2
)

Combine Product-Code Components

=TEXTJOIN(
"-",
TRUE,
A2,
B2,
C2
)

Combine Nonblank Skills

=TEXTJOIN(
", ",
TRUE,
B2:F2
)

CONCAT vs TEXTJOIN

CONCAT	TEXTJOIN
Combines values directly	Combines values using a delimiter
Does not include an ignore-empty option	Can ignore blank cells
Suitable for simple combinations	Better for addresses, lists and codes

25. Using the TEXT Function for Formatting

TEXT converts a number or date into formatted text.

Format a Date

=TEXT(A2,"dd-mmm-yyyy")

Display a Month Name

=TEXT(A2,"mmmm")

Format a Number as Indian Currency Text

=TEXT(B2,"₹#,##0.00")

Create a Formatted Sales Message

=A2&" generated sales of "&TEXT(B2,"₹#,##0")

Create a Custom Order ID

="ORD-"&TEXT(A2,"0000")

If A2 contains 25, the result is ORD-0025.

26. Clean a Phone Number

Phone numbers may contain spaces, hyphens, parentheses and country codes.

Remove Spaces and Hyphens

=SUBSTITUTE(
SUBSTITUTE(
D2,
" ",
""
),
"-",
""
)

Remove Country Code and Symbols

=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
D2,
"+91",
""
),
"(",
""
),
")",
""
),
"-",
""
),
" ",
""
)

Return the Last 10 Digits

=RIGHT(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
D2,
" ",
""
),
"-",
""
),
"(",
""
),
")",
""
),
10
)

Validate the Cleaned Number

Suppose the cleaned phone number is stored in G2.

=IF(
AND(
LEN(G2)=10,
ISNUMBER(--G2)
),
"Valid",
"Check Phone Number"
)

27. Clean an Email Address

Remove Spaces and Convert to Lowercase

=LOWER(
SUBSTITUTE(
TRIM(C2),
" ",
""
)
)

Check Whether @ Exists

=IF(
ISNUMBER(SEARCH("@",C2)),
"Valid Structure",
"Check Email"
)

Check for @ and a Full Stop

=IF(
AND(
ISNUMBER(SEARCH("@",C2)),
ISNUMBER(SEARCH(".",C2))
),
"Check Passed",
"Invalid Email Structure"
)

This is a basic structural check, not complete email-address validation.

Extract Email Provider

=TEXTBEFORE(
TEXTAFTER(C2,"@"),
"."
)

28. Standardize Category Names

Suppose the Category column contains values such as Computer, Computers, PC and Laptop Computer. Use SUBSTITUTE or a mapping table to standardize them.

Simple SUBSTITUTE Example

=SUBSTITUTE(
A2,
"Computers",
"Computer"
)

Multiple Replacement Example

=SUBSTITUTE(
SUBSTITUTE(
A2,
"PC",
"Computer"
),
"Computers",
"Computer"
)

For many replacements, create a separate mapping table and use XLOOKUP or Power Query instead of building a very long SUBSTITUTE formula.

29. Using Flash Fill

Flash Fill detects a pattern from examples and automatically fills the remaining values.

How to Use Flash Fill

1. Enter the expected result beside the first record.
2. Begin typing the next expected result.
3. Press Ctrl + E.
4. Review every generated value.

Useful Flash Fill Tasks

• Extracting first names
• Extracting last names
• Combining names
• Changing capitalization
• Removing symbols from phone numbers
• Creating usernames
• Separating codes

Important Limitation

Flash Fill produces fixed values, not formulas. If the source data changes, Flash Fill results do not update automatically.

30. Recommended Data-Cleaning Workflow

Raw data
   ↓
Remove hidden characters
   ↓
Replace nonbreaking spaces
   ↓
Trim extra spaces
   ↓
Standardize capitalization
   ↓
Split combined fields
   ↓
Standardize categories and codes
   ↓
Validate cleaned values
   ↓
Convert results to fixed values if required

Complete Name-Cleaning Formula

=PROPER(
TRIM(
CLEAN(
SUBSTITUTE(
B2,
CHAR(160),
" "
)
)
)
)

Complete Email-Cleaning Formula

=LOWER(
SUBSTITUTE(
TRIM(
CLEAN(
SUBSTITUTE(
C2,
CHAR(160),
" "
)
)
),
" ",
""
)
)

31. Convert Formulas into Fixed Values

After checking the cleaned data, you may need to replace formulas with their calculated values.

1. Select the cleaned data.
2. Press Ctrl + C.
3. Right-click the same location.
4. Select Paste Special → Values.

Keep a backup of the original data before permanently replacing formulas.

32. Common Text-Cleaning Mistakes

• Deleting the original raw data
• Using TRIM without checking for character-160 spaces
• Assuming CLEAN removes every possible hidden Unicode character
• Applying PROPER to abbreviations without reviewing the results
• Using fixed MID positions when text lengths vary
• Removing symbols before understanding their purpose
• Converting formulas to values before checking results
• Using Flash Fill without reviewing exceptions
• Mixing cleaned and uncleaned records in the same column
• Overwriting imported data without keeping a backup

33. Practical Assignment: Customer Data-Cleaning System

Create a professional cleaning system using at least 100 customer records.

Required Worksheets

• Raw_Customer_Data
• Cleaned_Customer_Data
• Validation_Report

Required Cleaned Columns

• Clean Customer ID
• Clean Full Name
• First Name
• Last Name
• Clean Email
• Email Username
• Email Domain
• Clean Phone Number
• Product Abbreviation
• Region
• Product Year
• City
• State
• Country

Assignment Tasks

1. Preserve the original raw data.
2. Remove hidden and nonprinting characters.
3. Replace nonbreaking spaces.
4. Remove unnecessary spaces.
5. Standardize customer names.
6. Convert email addresses to lowercase.
7. Remove spaces from email addresses.
8. Extract email usernames and domains.
9. Clean phone numbers and return the last 10 digits.
10. Split product codes into Product, Region and Year.
11. Split locations into City, State and Country.
12. Validate customer IDs and phone numbers.
13. Highlight invalid records using conditional formatting.
14. Convert final formulas into values after verification.

34. Practice Exercises

Exercise 1: Clean Employee Names

Remove hidden characters and unnecessary spaces, then convert names to proper capitalization.

=PROPER(
TRIM(
CLEAN(A2)
)
)

Exercise 2: Extract Product-Code Components

Split LAP-EAST-2026 into Product, Region and Year using TEXTSPLIT.

=TEXTSPLIT(A2,"-")

Exercise 3: Extract Email Information

Extract the username and domain from an email address using TEXTBEFORE and TEXTAFTER.

Exercise 4: Clean Phone Numbers

Remove spaces, hyphens, parentheses and country-code formatting from a list of phone numbers.

Exercise 5: Create Employee Codes

Create an employee code using the first three letters of the department, the last four digits of the employee ID and the joining year.

=UPPER(
LEFT(B2,3)
)
&"-"&
RIGHT(A2,4)
&"-"&
TEXT(C2,"yyyy")

35. Chapter Quiz

1. What does the LEN function count?
2. What is the difference between LEFT, RIGHT and MID?
3. What is the difference between FIND and SEARCH?
4. What type of spaces does TRIM remove?
5. Why is SUBSTITUTE sometimes required with CHAR(160)?
6. What does CLEAN remove?
7. What is the difference between SUBSTITUTE and REPLACE?
8. Which function extracts text before a delimiter?
9. Which function splits text into multiple cells?
10. What is the difference between CONCAT and TEXTJOIN?
11. Why should PROPER results be reviewed?
12. What is the main limitation of Flash Fill?

36. Frequently Asked Questions

Why does TRIM not remove every visible space?

The data may contain nonbreaking spaces represented by character 160. Replace them with standard spaces using SUBSTITUTE before applying TRIM.

What is the difference between FIND and SEARCH?

FIND is case-sensitive and does not support wildcards. SEARCH is not case-sensitive and supports wildcard matching.

Should I use MID or TEXTSPLIT?

Use MID when character positions and lengths are fixed. Use TEXTSPLIT when data contains reliable delimiters such as commas, hyphens or pipes.

Does Flash Fill update when the source data changes?

No. Flash Fill creates fixed results. Use formulas or Power Query when the output must update automatically.

Can Excel fully validate an email address?

Excel can check basic structural elements such as @ and a domain separator, but complete email-address validation requires more advanced rules or external verification.

Should raw data be deleted after cleaning?

No. Preserve the original data on a separate worksheet or file so that cleaning results can be audited and corrected.

Conclusion

Text functions provide a powerful toolkit for transforming inconsistent data into a reliable business dataset. LEFT, RIGHT and MID extract characters, while FIND and SEARCH locate specific text. TRIM, CLEAN and SUBSTITUTE remove unwanted spaces and hidden characters.

TEXTBEFORE, TEXTAFTER and TEXTSPLIT simplify the separation of combined values. CONCAT and TEXTJOIN combine fields into professional codes, addresses and descriptions. By combining these functions, you can clean customer records, employee lists, product codes, email addresses and phone numbers efficiently.

In the next chapter, we will study Excel date and time functions, including TODAY, NOW, DATE, DATEDIF, YEARFRAC, EDATE, EOMONTH, WORKDAY and NETWORKDAYS.