Find And Replace from a List of Multiple Emails in Excel without VBA

I had a recent request from a friend who needed to Find and Repace from a list of old emails with new emails.  I think her use case was that she was trying to anonymize the data that she was going to send to a vendor.  Therefore, she needed to remove a list of emails addresses from a column of data.

I think that this should be an Excel Option, but I can’t find it.  Let me know if I am wrong in the comments below.  Most people would have to write VBA code or buy a program to Find and Replace from a list, but there is a simple way to do this within Excel basic functions.  Check out below to learn this technique to save you time from using the standard 1-by-1 Find and Replace method, VBA or spending cold hard cash.

The Breakdown

1) Put Original Data in Column A

2) Copy / Paste with Transpose List of Find and Replace Values in Rows

3) Create Substitute Formula

4) Copy/Paste Formula across Start and End of Replace List

5) Use Updated Text in Final Column of Substituted Formulas

Step-by-Step

1) Put Original Data in Column A

The real trick to this solution is to put your data in a table based format with your original text on the left and your New and Old values you want to find/replace as rows of data instead of columns.

So, first, put your original text in a column.  For our sample, I put the data in Column A.

This technique is very simple to find and replace from a list no matter the text from which you are starting.  For instance, it is difficult to write find functions in the sample you see above because the email data is not the same in each row (i.e. there is not always a space or the same character before or after the email address).

2) Copy / Paste with Transpose List of Find and Replace Values in Rows

As stated above, we need to put our Find and Replace from a List values in the top of our data range (i.e. above our original data.  If your data is in a column format, simply

If your data is in a column format, simply copy the data and navigate to the new range and then press ALT+E+S to bring up the Paste Specials dialog box.  Then select Transpose checkbox and press ok.

This will move your data from a Column to a Row format.

3) Create Substitute Formula

Now that you have your data set up in the right pattern (Original Data on the Left Column and the Old and New text in Rows) you are ready to create a formula that will perform the Find and Replace from a List using Substitute Function in Excel.

If your Original Data is in Column A and your Old Values start in Row B1 and the New Values start in cell Row B2, then you can enter this formula in cell B3:

=SUBSTITUTE(A3,B$1,B$2)

Not that the New and Old Text sections of the Substitute Function have the Row locked as an Absolute reference.  If you don’t know or understand Relative and Absolute References, check out this tutorial: Absolute and Relative References in Excel Formulas.

4) Copy/Paste Formula across Start and End of Replace List

Now that we have our formula created, just copy it across all columns until the end of your replacement values and copy it down all rows to the end of your original data set.

The beauty of this formula is that it will substitute the new and old value in the text to the left.  And it will keep checking the values to the left until it finds a match, and since we are using a relative reference for the original data, a new reference is checked and replaced if matching as the formula moves to the right.

5) Use Updated Text in Final Column of Substituted Formulas

Your final column of formulas represents the fully converted data with the Find and Replace from a List action completed across the range.

Use that final column of data as your new data set.  You can repeat this operation for many sets of original data and additional Find and Replace Lists.

Sometimes just thinking about the problem in a different way is the best.  Now once again, you can do this with VBA, but not everyone is a VBA Expert and this is a very simple technique using the right data setup and an uncomplicated Substitute function.

Video Demonstration

Check out this Video tutorial on the techniques presented above.

Sample File Download

Click here to Download the Free Sample Excel Template File:
Find-And-Replace-from-a-List-of-Multiple-Emails-in-Excel-without-VBA.xlsx

If you have Sample VBA code that you like to use for Find and Replace from a List instead, please put it in the Comments below.  Also, if there is a simple Array formula for this problem, put that too in the comments below.  All-in-All, leave me a comment below on what you think of this simple solution.

Steve=True