Text Functions for Data Cleaning

Hello! Welcome to our fifth lesson in the "Advanced Excel for Data Analysis" module.

In our last session, we explored how to use conditional functions like COUNTIF and SUMIF to perform targeted calculations. A key takeaway was that these functions rely on precise criteria to work correctly. For instance, if you're trying to count sales in the "North" region, but your data contains entries like "North " (with a trailing space) or "north" (in lowercase), your formulas might fail to capture all the relevant data. This is a very common challenge in data analysis.

Today, we'll tackle this problem head-on by learning how to clean and manipulate text. These skills are fundamental for preparing your data for analysis, ensuring accuracy and consistency.

Today's learning outcome: By the end of this lesson, you will be able to use text functions (LEFT, RIGHT, MID, CONCATENATE, TRIM) to clean and manipulate string data.

Why Text Manipulation is Crucial

As a data analyst, you will often receive data that is not perfectly structured. It might come from different sources, be entered manually, or exported from various systems. Common issues include:

Unwanted spaces before or after text.
Information combined into a single cell (e.g., "Firstname Lastname").
Important codes or identifiers embedded within longer text strings (e.g., "Product ID: XZ-123").

Text functions are your tools for tidying up this messy data, making it usable for the kind of analysis we've been learning about.

Basic Cleaning and Joining

Let's start with two of the most frequently used text functions: one for cleaning up spaces and another for joining text together.

TRIM(): This function is incredibly simple but powerful. It removes all extra spaces from text, including leading and trailing spaces, and reduces multiple spaces between words to a single space.
CONCATENATE() or the & operator: This function joins two or more text strings into one. It's perfect for creating a full name from separate first and last name columns, or for constructing unique identifiers. The ampersand (&) is a common shortcut for concatenation.

The image below shows a simple example of how TRIM and CONCATENATE are used to clean and combine names.

Excel CONCATENATE and TRIM Functions Demonstration — This image shows four different ways to combine text. The first example uses a simple CONCATENATE. The second shows how TRIM can clean up the result. The final two examples demonstrate how to build a clean string from messy source cells by trimming them and adding a space in between.

Extracting Text: LEFT, RIGHT, and MID

Sometimes, instead of joining text, you need to break it apart. Excel provides three key functions for this, based on the position of the text you want to extract:

LEFT(text, num_chars): Extracts a specific number of characters from the beginning (left side) of a text string.
RIGHT(text, num_chars): Extracts a specific number of characters from the end (right side) of a text string.
MID(text, start_num, num_chars): Extracts characters from the middle of a text string. You specify where to start and how many characters to get.

Let's watch a short video that introduces all the functions we've discussed so far.

Excel Formulas and Functions | Full Course

This video from Kevin Stratvert provides a concise overview of the most common text functions. It's a great way to see the basic theory and syntax for each one.

Watch the segment from 00:32:00 to 00:38:30. The video will cover TRIM, CONCATENATE, LEFT, RIGHT, MID, and a few other helpful functions like LEN and FIND which we will use next.

Making Extraction Dynamic: Combining Functions

The examples we've seen so far are great, but they often rely on knowing the exact number of characters to extract. What if you have a list of product codes like "Item-1234" and "Product-567", and you only want the number? The length of the text before the hyphen changes.

This is where the real power of Excel comes in: combining functions. By nesting functions like FIND and LEN inside LEFT, RIGHT, and MID, we can create dynamic formulas that adapt to the data.

FIND(find_text, within_text): Returns the starting position of one text string within another. For example, FIND("-", "Product-567") would return 8.
LEN(text): Returns the total number of characters in a text string.

Let's see how to apply this logic in a practical scenario. The following video masterfully explains how to build dynamic formulas to split messy data into clean columns. It aligns perfectly with your preference for understanding the method before practicing it.

Left, Mid, Right, Len, Find Functions | with Different Digits in Each Item

This Jopa Excel video demonstrates how to solve a very common data cleaning task: splitting a single column of messy data. We'll watch it in three parts, focusing on extracting the beginning, end, and middle sections of the text.

Please watch the video in these three segments: Extracting from the Left (02:17 - 07:29): Pay close attention to how LEFT is combined with FIND to get the product name, regardless of its length. Extracting from the Right (07:39 - 14:23): Notice the logic used here. To get the ID from the end, the formula calculates the total length (LEN) and subtracts the position of the starting delimiter (FIND). Extracting from the Middle (14:23 - 22:07): This is the most complex part. See how MID is used with two separate FIND functions—one to determine the start_num and a calculation to determine the num_chars to extract.

To review these concepts in a written format, the following article provides a clear, beginner-friendly summary.

Data Cleaning with Excel: Essential Text Cleaning Functions Every Beginner Should Know

This Medium article, 'Data Cleaning with Excel', offers a great text-based summary of the functions we've covered.

Please read sections 1, 4, and 5. These cover 'Remove Extra Spaces' (TRIM), 'Combine Multiple Text Strings' (CONCATENATE), and 'Extract Text From A Cell' (LEFT, RIGHT, MID). This will help solidify your understanding.

Practice Exercise

Now it's your turn. Your experience with data entry means you've likely encountered data like this before. Let's clean it up.

Imagine you have the following data for new client sign-ups:

Full Name & Title	Email
" Mr. ali mahmoud"	ali.mahmoud88@example.com
"Ms. fatima zahra "	fatima.z@example.com
" dr. youssef el-sayed"	youssef.elsayed@example.com

Your Task:
Create a new Excel sheet with this data. Add new columns and use text functions to achieve the following:

Cleaned Full Name: In a new column, use functions to clean the Full Name & Title column so it appears with proper capitalization and no extra spaces (e.g., "Mr. Ali Mahmoud"). (Hint: The first video mentioned the PROPER function, which capitalizes the first letter of each word).
First Name: In another column, extract just the first name from your "Cleaned Full Name" column (e.g., "Ali").
Last Name: In a third column, extract the last name (e.g., "Mahmoud").
Email Username: In a final column, extract the username from the Email column (the part before the "@" symbol).

Take your time to build the formulas. Think about how to find the spaces and the "@" symbol to make your formulas dynamic.

Click here to see the solutions

Let's assume your data is in columns A and B, starting from row 2.

Cleaned Full Name (in C2):
- Formula: =PROPER(TRIM(A2))
- This first trims the extra spaces and then applies proper case capitalization.
First Name (in D2):
- Formula: =MID(C2, FIND(" ", C2) + 1, FIND(" ", C2, FIND(" ", C2) + 1) - FIND(" ", C2) - 1)
- This is a bit tricky! A simpler approach for these specific names is to find the first space (after the title) and the second space.
- A more robust formula might be needed for names with more parts, but this works for the given data. Let's try a simpler case for practice: Let's assume the title is gone and the name is "Ali Mahmoud". The formula would be: =LEFT(C2, FIND(" ", C2) - 1)
Last Name (in E2):
- Formula: =RIGHT(C2, LEN(C2) - FIND(" ", C2, FIND(" ", C2) + 1))
- Similar to the first name, this finds the second space and extracts everything after it.
- Simpler case for "Ali Mahmoud": =RIGHT(C2, LEN(C2) - FIND(" ", C2))
Email Username (in F2):
- Formula: =LEFT(B2, FIND("@", B2) - 1)
- This finds the position of the "@" symbol and extracts all characters to the left of it, subtracting 1 to exclude the "@" itself.

Conclusion

Excellent work! You have just mastered some of the most essential data cleaning tools in Excel. The ability to parse, clean, and restructure text is a non-negotiable skill for a data analyst. It's the critical first step that ensures the quality and integrity of all subsequent analysis.

Key Takeaways:

TRIM is your go-to function for removing unwanted spaces.
CONCATENATE and the & operator are used to join text from multiple cells.
LEFT, RIGHT, and MID are used to extract specific portions of a text string.
The true power of these functions is unlocked when you combine them with FIND and LEN to create dynamic formulas that adapt to varying text lengths.

In our next lesson, we will move from preparing data to presenting it. You'll learn how to create various charts (bar, line, scatter) to visualize data patterns in Excel, turning your clean numbers into compelling visual stories.