Anonymisation using excel macro / find and replace

[pete101]

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

 

[Joe4]

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.