tidy up Mc/Mac/O in Excel database

I got a question about ways to clean up the following..
someone has entered Mc Donald instead of McDonald or MacDonald. Or someone enters O Brien instead of O'Brien....is there a way to tidy up the data in Excel to the desired format? Thanks :-)
agwalshAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SimonConnect With a Mentor Commented:
The only way I know is to search for the strings, by dropping extra formula columns down the end of your table.
e.g. =OR(LEFT(A2,3)="Mc ",LEFT(A2,2)="o ",LEFT(A2,4)="mac ")
Then you can filter on TRUE in the new column to find all the ones to change.
You could do the replacement directly with a formula, but I find it safest to visually inspect the data first.
e.g. a sample formula for the first case you mentioned would be:
=SUBSTITUTE(A2,"Mc ","Mc",1)
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Apply a filter to the data and then on the Name column enable the filter and select the tick boxes for those that match the various options, ie Mc D, McD, MacD.

From those that are visible select one that is correct and copy. You can then select the remainder of the column and paste, so long as you have only selected one cell to copy, this will only paste into the visible cells.

Repeat for next option.
0
 
agwalshAuthor Commented:
Does the job both of them but just chose the SimonAdept solution as best solution for its elegance and flexibility. But both do the job.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.