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
- Open Microsoft excel and open the dataset. Go to the sheet titled “Employee Data”
- 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”
- 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”
- Find the Branch ID of each employee in Column A by using a text manipulation function to pull the last 2 digits
- Find the Pay period which is the last letter in the employee code
- 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
- Go to the sheet titled “Employees” and find all employees in Employee data using the data given
- Find the employee with the highest sales generated and put their name, employee ID and location under “Highest Sales”
Data Files
Solution
Challenge158_Solution.xlsx
Solution Video: Challenge 158|EXCEL – Text Manipulation
