Anonymisation using excel macro / find and replace

pete101

New Member
Joined
Oct 22, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I recently read the following article on using find and replace to correct typos in city names based on a list of corrections Find and Replace based on a List

I was wondering if the same principals couple be applied for a large scale anonymisation of a dataset replacing peoples names such as "Toby" or "Mr Jones" with "name" or "Mr name" without having to write an extremely long macro code below and mannually inserting every different name in to the What:="" section


Selection.Replace What:="toby", Replacement:="name", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

This would be in medical record data so the cell might contain a long string sentence like "I told toby to go to the pharmacy" and wanting the end product to be "I told name to go to the pharmacy"

Because there are thousands of different possible names it's not really feasible to do the above code for every name but a list is much more viable and seemed to be potentially possible based on the previous post. This list would need to be edited to include spaces before / after the name e.g. " Ali " as these three ordered letters could appear in multiple other words.

So hoping for find and replace worksheet that would have a list of names in column A e.g.
" Ali "
" Ali, "
" Ben "
" Steve "

all to be replace with "name" which could be inserted into every cell in column B unless there is an easier way to do it?

Thanks very much for any help / advice!

BW

Pete
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,315
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

I see some potential issues:
This list would need to be edited to include spaces before / after the name e.g. " Ali " as these three ordered letters could appear in multiple other words.

What if the name were to appear at the beginning or end of a sentence?
Then it would not have a space before or after it.

And because there is an endless supply of possible names, it seems like you would constantly be having to update the list.
That does not seem like a great solution to me (one that includes constant, intensive maintenance).

And what happens when you have names that are also valid words (i.e. "Drew"or "Bill")?
It could render the sentence totally nonsensical!

I work in the health care business myself where privacy is critical too.
It seems to me that the only really reliable method would be to either handle every entry manually, or better yet, not include this information at all.

That being said, if you really wanted to proceed with your original idea, it is possible. Just realize that you are going to run into the issues that I brought up.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,237
Messages
5,836,181
Members
430,406
Latest member
pmav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top