Excel Challenge 12: Methods for Splitting Cell Values

Welcome to the first challenge of the new year! This Excel challenge is beginner-friendly, but you can turn up the heat by timing yourself. 

Here’s how it works:

• First, watch the challenge video and read the instructions below the video
• Review the previously published video or article suggested
• Download the Excel worksheet you will use to complete the challenge tasks
• Put yourself to the test!

Want to chat about your approach and process with other Excel heads? Join our Slack channel to share your insights and questions with like-minded learners.

The challenge

On the worksheet below we have the top 20 cities in the world in terms of population. In one column we have the city and two-letter country code and in the next column the population in 2021.

You will need to:

1. Split the city and country data so that they’re in two separate columns.
2. Sort the list by country code. Any country that appears more than once in the list should be sorted by city name.

There will be multiple approaches to get the correct answer, so let's see how many you can find. And tell us which one you found to be the quickest. We've included the video on how to merge and split cells to help you out.

You can also read this article for more details: How to Merge Cells and Split Cells in Excel

Download the file below to get started.

The countdown is on! Can you meet this challenge before we release the solution? Share your thoughts and progress with me on the GoSkills Slack channel, and stay tuned for the solution!

The solution

We hope you enjoyed taking part in this challenge!

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical, real-world Excel skills.