Skip to content

Excel Challenge 11 – Data Cleaning

Welcome to Excel Challenge number eleven!

You will find four questions in this article designed to test your ability to clean data in Microsoft Excel.

Click the link below to download the spreadsheet and test your skills.

Excel Challenge 11 Workbook

Question 1

Question one asks you to split the comma-separated data into the appropriate columns.

Question 1 – Solution

To solve question one, start by selecting all the data in B4:B21. Then go to Data Text to Columns.

Choose the Delimited option, and click Next.

Select Comma, and click Next.

Enter in $D$4 for the destination, and click Finish.

If Excel shows you a warning, click on Ok.

Excel separates the data and places it into the appropriate columns.

Question 2

For question two, your task is to remove any duplicate records from the dataset.

Question 2 – Solution

To solve question two, start by selecting the data. Then go to Data > Remove Duplicates.

Make sure all of the columns are checked, and click Ok.

Excel lets you know how many duplicate values it found and removed. Click on Ok to finish.

Question 3

Question three asks you to format the phone numbers in column F. You must also write two formulas to split up the first name and last name from the full name column.

Question 3 – Solution

To solve question three, start by selecting all of the phone numbers. Then right-click and Format Cells.

Under the number tab, select Special. Select Phone Number, and click Ok.

Now for the formulas. Select cell D4, and enter the following formula.

=TEXTBEFORE(C4, " ")

The TEXTBEFORE function looks at the value in C4 (1st argument) and returns only the text that comes before the space (2nd argument).

As a result, you get back the first name.

For the last name, select cell E4, and enter the following formula.

=TEXTAFTER(C4, " ")

The TEXTAFTER function looks at the value in C4 (1st argument) and returns the text that comes after the space (2nd argument).

As a result, you get back the last name.

To complete question three, select D4:E4, and use the fill handle to copy the formulas down for the rest of the data.

Question 4

For question four, you must split the address information into street, city, state, and zip code. You must then delete the address column.

Question 4 – Solution

I highly recommend using Flash Fill to complete question four.

Start by typing in the street, city, state, and zip code components for the first record in the dataset.

Then select cell G5 and use the keyboard shortcut CTRL+E. This command activates flash fill, and Excel completes the rest of the street data for you by following the example you set for the first record.

Complete the rest of the data using the same method.

Select cell H5 and use CTRL+E.

Then select cell I5 and use CTRL+E.

And finally, select cell J5 and use CTRL+E.

You have successfully split up the address information. All that’s left is to select column F, right-click, and delete the column.

How Did You Do?

Leave me a comment below to let me know how you did! I’d be curious to know if you found these tasks difficult or easy.

Also, if you missed any of the other Excel challenges, click on the link below to view them all!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top