158|EXCEL – Text Manipulation

BYU Student Author: @Carter_Lee
Reviewers: @Benjamin_Lau @Dallin_Gardner
Estimated Time to Solve: 10 Minutes

This is an intro challenge that is part of the Excel Learning Path.

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

Overview
SmartHome Co. sells smart home appliances including doorbells, CCTVs, smart light bulbs, home security products, etc. SmartHome has an international salesforce and generates millions of sales each year. You are given the dataset of SmartHome employees including their information, performance, and satisfaction scores. In this challenge, you will be using text manipulation functions in Excel to uncover interesting facts about Smart Home and its employees.

Instructions

  1. Open Microsoft excel and open the dataset. Go to the sheet titled “Employee Data”
  2. Find the location of each employee in Column A by using a text manipulation function to split the location which is the first three letters of their Employee code to Column D titled “Location”
  3. Find the Employee ID of each employee in Column A by using a text manipulation function like mid separate the employee which is the first four letters of their Employee code to Column D titled “EmpID”
  4. Find the Branch ID of each employee in Column A by using a text manipulation function to pull the last 2 digits
  5. Find the Pay period which is the last letter in the employee code
  6. Find the Location Branch ID which is the Location followed by a dash and branch ID which can be found in the Employee Code in Column A
  7. Go to the sheet titled “Employees” and find all employees in Employee data using the data given
  8. Find the employee with the highest sales generated and put their name, employee ID and location under “Highest Sales”

Data Files

Solution

This is a short and sweet challenge! I was able to refresh my knowledge on lookups and text functions. Here are screenshots

Highest Sales is Karl Fleming in the AUS location with the EmpID 1008! I loved this exercise.

It was good to refresh my lookup abilities! Here are my screenshots.


Cool challenge, here’s my solution.
Challenge158_Data.xlsx (89.6 KB)

This was a good challenge! It took me about 15 minutes to complete!


Time: 12 minutes
Rating:10/10
Comments: This was a great refresher for basic excel functions

Here is my solution:
Challenge158_Data- DUFFY.xlsx (89.2 KB)

I finished the challenge in 17 minutes.

Rating: Intermediate

I learned a lot and the challenge was really fun to finish.



Challenge158_Data.xlsx (89.6 KB)

techhubchallenge.xlsx (92.9 KB)
This is my solution

Great practice!
This is my solution:

1 Like

20 Mins, Easy, I enjoyed refreshing my memory!


Time to Complete: 10 minutes
Difficulty: Beginner!
Great challenge to use filters, sorting, and basic functions.
SONA challenge 158.xlsx (98.2 KB)


This was a good one! Fun way to revisit my excel shortcuts