Last week, in my Contextures Newsletter, I posted an Excel Name Fix challenge. There was a short list of names, which needed to be fixed. Then we needed to count how many were fixed. Take the challenge yourself – download the workbook to see how you’d solve it.
Excel Name Fix Challenge
Here the challenge that I posted in the newsletter —
If people enter their names in a sign-up form, you might end up with a mixture of upper and lower case letters. You can see an example in the screen shot below.
This week’s challenge is to clean up that list. Using Excel formulas, make these changes to the list:
- Show the corrected name in column B, with only the first letter of each name in upper case. For example, Fred Jones instead of FRED JONES.
- Add an X in column C, to mark the names that were fixed.
- In cell E1, show the number of names that were fixed.
For inspiration, there are videos and links on my Functions page.
Fixing the Problem
Several people sent their solutions to me:
- a few said the problem was very easy to solve
- others had a bit of trouble, and had to do some research to find a function that would solve the problem.
- a couple knew the simple ways to fix the problem, and looked for different formula solutions
There were a couple of unexpected twists too:
- one person noticed an extra space in a name, and wanted to fix that too
- another person asked if there was a way to show the number of changes in each name
I compiled the solutions into the Excel Name Fix Solutions workbook.
Take the Excel Name Fix Challenge
To take the challenge, download the completed Name Case Solution workbook. The zipped file is in xlsx format, and does not contain any macros.
- The problem is on the NameList sheet.
- Don’t look at the other sheets, until you’re ready to see the solutions to this challenge.
Let me know if you come up with any other formulas that solve the problem. And don’t read the comments below, if you don’t want to see a solution!