Data cleansing is a critical step in data management, ensuring that your data is accurate, consistent, and usable. Excel provides powerful functions like TRIM, LEFT, RIGHT, MID, and CONCAT to help clean and merge data effectively.
Use TRIM, LEFT, RIGHT, MID, and CONCATENATE to Clean and Merge Data
The TRIM function removes extra spaces from text, while LEFT, RIGHT, and MID functions extract specific parts of a string. CONCATENATE (or the newer CONCAT function) merges multiple strings into one. These functions are essential for preparing your data for analysis.
Example
Below is an example dataset showing raw data that needs to be cleaned and merged. This dataset will be used to demonstrate how to apply these functions.
| Raw Data | First Name | Last Name | |
|---|---|---|---|
| John Doe | John | Doe | [email protected] |
| Jane Smith | Jane | Smith | [email protected] |
Exercise
Clean and merge the following raw data using TRIM, LEFT, RIGHT, MID, and CONCATENATE functions:
Use the functions to extract the first name, and last name, and create an email address in lowercase format.
Solution
To clean and merge the data, follow these steps:
Apply Functions:
First Name: Use the TRIM and LEFT functions to extract the first name.
=TRIM(LEFT(A2,FIND(" ",A2)))
=TRIM(LEFT(A3,FIND(" ",A3)))Last Name: Use the TRIM and MID functions to extract the last name.
=TRIM(MID(A2,FIND(" ",A2)+1,LEN(A2)))
=TRIM(MID(A3,FIND(" ",A3)+1,LEN(A3)))Email: Use the CONCATENATE (or CONCAT) and LOWER functions to create the email address.
=LOWER(CONCATENATE(TRIM(LEFT(A2,FIND(" ",A2))), ".", TRIM(MID(A2,FIND(" ",A2)+1,LEN(A2))), "@example.com"))
=LOWER(CONCATENATE(TRIM(LEFT(A3,FIND(" ",A3))), ".", TRIM(MID(A3,FIND(" ",A3)+1,LEN(A3))), "@example.com"))