Overview
Your boss, the Chief Human Resource Officer, has asked you to provide a list of employees for a client. Before you share the data with the client; however, she has asked you to transform some of the sensitive information to protect the employees. The client is eager for the data, and doing this task manually would take too long for their liking. With the right formulas, you should be able to impress both your boss and make the client happy.
Instructions
Below is a list of requirements from your boss:
The client wants a separate column for both first and last names. Use a formula to split the names into the First Name and Last Name Columns in Columns F and G respectively.
The client also wants to only show the Birth Year of each employee. Use a formula to extract the birth year from the BirthDate column into the new Birth Year column (column H).
The client doesn’t think each employee’s SSN should be visible. Use =REPLACE to replace the first 5 digits of each SSN with a “x” while keeping the final four digits into the SSN Last 4 column (column I).
Finally, from the HireDate column, parse the date into three different columns. One for the day, month, and year.
I like text manipulation in Excel because there are so many fun patterns to exploit. I did this a bit different than your solution. See the image showing the formulas below.
I think text manipulation is very powerful, especially when it comes to cleaning data. It is an essential skill, even if it’s not always the most fun. This challenge reminded me of the “Replace” function, which I think is quite helpful. I did that portion of the challenge without any concatenation. I like using the Left and Right functions, but gave this challenge a try using mostly Mid. It worked pretty well! Take a look:
I always appreciate a good reminder of some of the text manipulation you can do in excel. This challenge was a good quick fix at remembering how to use a lot of those formulas you highlighted. I ended up using SEARCH instead of FIND but it worked great.
Great challenge, Klayton. I’ve always been intimidated by most of the formulas you specified in your suggestions and hints, but I felt like living on the edge today. Definitely got my adrenaline going, what a rush! The hardest part for me was figuring out how to show the formulas in only the second row. I knew about the “show formulas” shortcut and thought maybe it had something to do with that, but I ended up just opting to throw an apostrophe in front of each cell. Someone let me know if there’s another secret I don’t know about! Here’s what my formulas looked like:
Definitely a tricky challenge. I forgot these functions even existed in excel, happy I got to practice! Here is my solution. Thanks for your solution video as well, it helped me understand some of the trickier text manipulation skills.
Fun challenge. I haven’t used text manipulation in excel for a couple of months and this was a great refresher. I did notice I did it a little differently than the other solutions but it’s always good to know the different methods that excel has to offer.
I’ve never used a couple of these formulas, but I was able to figure it out due to them being pretty straight forward. They’re very helpful, so thanks for showing me them.
Wow, its amazing how much you can forget in one semester. I did not finish as fast as I would have liked but here is my solution, it is not fancy as some others on here, but I think it works!
This was a great challenge! I haven’t had much experience with some of these formulas so it was great practice. Thanks for putting this together! Here is my solution with the formulas I used.
This was a fun challenge to remind me of these useful formulas. After trying a few complicated formulas that seemed too long-winded and complex, I was able to find simpler ones to complete the challenge and improve my knowledge of excel formulas.
This was a nice refresher on some of the essential text parsing functions! For the hire date parsing I actually ended up using the datevalue function and adjusting the text so it would be recognized as a date. Great challenge!
A fun challenge. For the longest time, I was confused why my Birth Year column would only return 1905, but then I realized that my column was formatted as a date too which was messing things up. Once I changed to General, it fixed it and I could see the correct years.
It’s so cool to see how many different ways there are to get to the same answer in Excel. I did the dates different than everyone else. I tried using the MID function to get HireDay, for instance, but it didn’t work. I think that’s because my BirthDate and HireDate columns are formatted as dates rather than as string, but I’m not totally sure. Here are my formulas, so if anyone has any wisdom for me, I’d love to hear it!
I really love Excel and all the various ways to get to the same conclusion. I enjoyed looking at everyones solutions and I loved your video Klayton it had some super necessary reminders for me. One thing I would like to mention is that for the Birth Year, the format the data was stored in wouldn’t allow me to use the RIGHT function. When I changed the format of the data it worked, but it also worked to use YEAR instead, which is what I ended up doing.
I found this exercise to be pretty straightforward. I think the most helpful thing for me to remember to accurately change the data is that you can do the formula calculations in a different cell and then copy/paste only the values (rather than the formulas). It made changing all the social security numbers super easy. Challenge13_Transform_Your_Data!.xlsx (520.1 KB)
