Solved

tidy up Mc/Mac/O in Excel database

Posted on 2015-02-20
3
33 Views
Last Modified: 2015-02-24
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 :-)
0
Comment
Question by:agwalsh
3 Comments
 
LVL 18

Accepted Solution

by:
SimonAdept earned 250 total points
ID: 40620769
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
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 40621518
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
 

Author Closing Comment

by:agwalsh
ID: 40627618
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now