Text Extraction and Data Cleaning Practice Question with Solutions

Introduction

Real-world Excel data is often messy. Names may contain extra spaces, emails may be mixed with other information, product codes may have different formats, and important details may be hidden inside longer text. In this chapter, you will practice extracting and cleaning data using functions such as LEFT, RIGHT, MID, FIND, SEARCH, TRIM, CLEAN, SUBSTITUTE, TEXTBEFORE, TEXTAFTER, and TEXTSPLIT. These questions are designed to help you handle practical Excel data-cleaning tasks. Text Extraction and Data Cleaning

Practice question with solutions to help you understand the concepts.

Question 1: Extract Username from Email Address

Problem Statement

A customer database contains email addresses. Extract the username appearing before the @ symbol.

Excel Data

Solution

In B2, enter:

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

Copy the formula down.

Expected Output

Question 2: Extract Domain from Email Address

Problem Statement

Extract everything after the @ symbol from each email address.

Excel Data

Solution

In B2, enter:

=RIGHT(A2,LEN(A2)-FIND("@",A2))

Copy down.

Expected Output

Question 3: Extract Product Category from a Product Code

Problem Statement

Product codes follow this format:

ELEC-LAP-101

The first part represents the product category. Extract the category.

Excel Data

Solution

In B2, enter:

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

Copy down.

Expected Output

Question 4: Extract Product Number from the End of a Code

Problem Statement

The last three characters of each product code represent the product number. Extract that number.

Excel Data

Solution

In B2, enter:

=RIGHT(A2,3)

Copy down.

Expected Output

Question 5: Extract the Middle Section Using MID

Problem Statement

Each employee ID follows this format:

EMP-DEL-101

The middle section represents the city code. Extract it.

Excel Data

Solution

In B2, enter:

=MID(A2,5,3)

Copy down.

Expected Output

MID starts at a specified position and extracts a specified number of characters.

Question 6: Clean Names with TRIM and PROPER

Problem Statement

Employee names were imported from another system. They contain extra spaces and inconsistent capitalization. Clean and standardize the names.

Excel Data

Solution

In B2, enter:

=PROPER(TRIM(A2))

Copy down.

Expected Output

Question 7: Remove Unwanted Characters from Phone Numbers

Problem Statement

Phone numbers have been stored using different separators. Convert them into a consistent format containing only the digits.

Excel Data

Solution

For values containing hyphens, spaces, or periods, use nested SUBSTITUTE functions.

In B2, enter:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),".","")

Copy down.

Expected Output

This approach removes the three unwanted separators.

Question 8: Extract Name and City from Combined Data

Problem Statement

Customer information is stored in this format:

Rahul Sharma - Delhi

Extract the customer name and city into separate columns.

Excel Data

Solution

Customer Name

In B2, enter:

=TEXTBEFORE(A2," - ")

City

In C2, enter:

=TEXTAFTER(A2," - ")

Copy both formulas down.

Expected Output

Note: TEXTBEFORE and TEXTAFTER are available in newer Excel versions.

Question 9: Split a Full Name into Separate Columns

Problem Statement

Names are stored in a single cell. Split the first name, middle name, and last name into separate columns.

Excel Data

Solution

In B2, enter:

=TEXTSPLIT(A2," ")

Excel will automatically spill the three parts into the adjacent cells.

Expected Output

TEXTSPLIT is useful when a single cell contains multiple pieces of information separated by a delimiter.

Question 10: Complete Data Cleaning and Extraction Task

Problem Statement

You receive customer records in this format:

RAHUL SHARMA | rahul@gmail.com | DELHI

The data contains extra spaces and inconsistent capitalization.

Create three clean columns:

1. Customer Name
2. Email
3. City

Excel Data

Solution

Customer Name

In B2, enter:

=PROPER(TRIM(TEXTBEFORE(A2,"|")))

Email

In C2, enter:

=LOWER(TRIM(TEXTBEFORE(TEXTAFTER(A2,"|"),"|")))

City

In D2, enter:

=PROPER(TRIM(TEXTAFTER(A2,"|",2)))

Copy all three formulas down.

Expected Output

This combines text extraction and data cleaning in one practical example.

Key Takeaways

• LEFT extracts characters from the beginning of text.
• RIGHT extracts characters from the end.
• MID extracts characters from a specific position.
• FIND can locate a character or delimiter before extracting text.
• SEARCH can also locate text without case sensitivity.
• TRIM removes unnecessary spaces.
• CLEAN removes non-printable characters.
• SUBSTITUTE can remove or replace unwanted characters.
• TEXTBEFORE extracts text before a specified delimiter.
• TEXTAFTER extracts text after a specified delimiter.
• TEXTSPLIT can split one cell into multiple cells.
• PROPER is useful for standardizing names and city names.
• LOWER is useful for standardizing email addresses.
• Combining extraction and cleaning functions is common when preparing imported data for analysis.

FAQs

1. What is text extraction in Excel?

Text extraction means taking a specific part of a larger text value. For example, extracting the username from an email address or the city from an address.

2. Which Excel functions are used for text extraction?

Common functions include:

LEFT
RIGHT
MID
FIND
SEARCH
TEXTBEFORE
TEXTAFTER
TEXTSPLIT

3. How can I extract text before a specific character?

In newer Excel versions, you can use TEXTBEFORE.

=TEXTBEFORE(A2,"@")

For older Excel versions, you can combine LEFT and FIND.

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

4. How can I extract text after a specific character?

Use TEXTAFTER in newer Excel versions.

=TEXTAFTER(A2,"@")

For older versions, RIGHT, LEN, and FIND can be combined.

5. How do I remove extra spaces from imported data?

Use TRIM.

=TRIM(A2)

For names, you can combine it with PROPER:

=PROPER(TRIM(A2))

6. How do I remove hyphens from a phone number?

Use SUBSTITUTE.

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

For multiple unwanted characters, you can nest SUBSTITUTE functions.

7. What is TEXTSPLIT used for?

TEXTSPLIT separates text into multiple cells using a delimiter.

For example:

=TEXTSPLIT(A2," ")

splits text wherever a space occurs.

8. What is the difference between TEXTBEFORE and LEFT?

LEFT extracts a specified number of characters, while TEXTBEFORE extracts everything before a specified delimiter.

For example:

=LEFT(A2,5)

extracts five characters, whereas:

=TEXTBEFORE(A2,"-")

extracts everything before the hyphen.

9. Why should data be cleaned before analysis?

Messy data can cause problems with sorting, filtering, searching, matching, and calculations. Cleaning the data first makes it more consistent and easier to analyze.

10. Can text extraction and data cleaning functions be combined?

Yes. Combining functions is one of the most useful Excel techniques for real-world data.

For example:

=PROPER(TRIM(TEXTBEFORE(A2,"|")))

extracts the required part and cleans its spaces and capitalization at the same time.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar