Take the Excel Name Fix Challenge

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:

  1. 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.
  2. Add an X in column C, to mark the names that were fixed.
  3. In cell E1, show the number of names that were fixed.

For inspiration, there are videos and links on my Functions page.

fix the names challenge

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.

name fix solutions workbook

 

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!

___________________

 

Excel Name Fix Challenge http://blog.contextures.com/

Save

You may also like...

8 Responses

  1. David N says:

    The CharCount formula on your Solutions04 worksheet was not array entered into the table, so it’s showing the wrong answers. And a non-array alternative for that CharCount is as follows.

    =SUMPRODUCT(–NOT(EXACT(MID([@Name],ROW(INDEX(A:A,1):INDEX(A:A,LEN([@Name]))),1),MID([@Fixed],ROW(INDEX(A:A,1):INDEX(A:A,LEN([@Name]))),1))))

    • Thanks David! I’ve fixed the array formula and added your non-array solution.

      • David N says:

        Awesome! And if anyone is interested in a formula for counting the total number of character changes including any TRIM operation, then here is one for that as well.

        =SUMPRODUCT(–NOT(EXACT(MID([@Name],ROW(INDEX(A:A,1):INDEX(A:A,LEN([@Name]))),1),MID(PROPER([@Name]),ROW(INDEX(A:A,1):INDEX(A:A,LEN([@Name]))),1))))+(LEN([@Name])-LEN(TRIM([@Name])))

  2. exshail says:

    BTW you can just reduce the formula within Table by writing field name in bracket as under:

    In column B = PROPER([Name])
    In column C = IF(EXACT([Name],[Fixed]),””,”X”)

  3. Walt says:

    Did this via Power Query which will take in to account any new records added to the table. Only formula used was CountIf to get the “# Changed”.

    Below are the Power Query steps:
    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“Name”, type text}, {“Fixed”, type any}, {“Changed?”, type any}}),
    #”Duplicated Column” = Table.DuplicateColumn(#”Changed Type”, “Name”, “Name – Copy”),
    #”Capitalized Each Word” = Table.TransformColumns(#”Duplicated Column”,{{“Name – Copy”, Text.Proper}}),
    #”Trimmed Text” = Table.TransformColumns(#”Capitalized Each Word”,{{“Name – Copy”, Text.Trim}}),
    #”Cleaned Text” = Table.TransformColumns(#”Trimmed Text”,{{“Name – Copy”, Text.Clean}}),
    #”Replaced Value” = Table.ReplaceValue(#”Cleaned Text”,” “,” “,Replacer.ReplaceText,{“Name – Copy”}),
    #”Removed Columns” = Table.RemoveColumns(#”Replaced Value”,{“Fixed”, “Changed?”}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“Name – Copy”, “Fixed”}}),
    #”Added Custom” = Table.AddColumn(#”Renamed Columns”, “Changed?”, each if [Name] = [Fixed] then null else “X” )
    in
    #”Added Custom”

Leave a Reply

Your email address will not be published. Required fields are marked *