Use this quick Excel hack to speed up data prep.

My real-world use case: client has 2 multi-select picklist fields that are being combined, then the values are being replaced with new values.

• I can’t use VLOOKUP because there are multiple values separated by semicolon
• I can’t use regular find and replace because that’s one-by-one and would take forever

So, we’ll add in a snippet of VBA (fyi that stands for Visual Basic for Applications, which is Excel’s programming language, but if you’re like me you’re thinking NO ONE CARES GET ON WITH IT.)

Create a list of the old values and the corresponding new values

Paste this somewhere convenient for reference in the sheet you want to update.

Right click on the tab of the Excel sheet you want to complete the find and replace on. Click “View Code.”

An empty VBA code box appears.

Paste the following VBA code into the box

Option Explicit
Sub FindnReplaceMultipleValues()
  Dim Rng As Range
  Dim OldText As Range
  Dim ReplaceData As Range
  On Error Resume Next
  Set OldText = Application.InputBox("Select Old Text Range:", "Find And Replace Multiple Values", Application.Selection.Address, Type:=8)
  Err.Clear
  If Not OldText Is Nothing Then
    Set ReplaceData = Application.InputBox("Replace What And With:", "Find And Replace Multiple Values", Type:=8)
    Err.Clear
    If Not ReplaceData Is Nothing Then
      Application.ScreenUpdating = False
        For Each Rng In ReplaceData.Columns(1).Cells
          OldText.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
        Next
      Application.ScreenUpdating = True
    End If
  End If
End Sub

The VBA code box will now look like this.

Click F5 to run the code

Next, click F5. If you’re on a Mac, hold down the fn (function) button in the lower left corner and click the F5 button across the top of the keyboard.

You will see a dialog box that prompts you to “Select Old Text Range:”.

Select the cells in the column to be replaced

In the above example, we want to replace the value of cells E2 to E13.

Click the “ok” button in the dialog box, and a second dialog box appears that says “Replace What And With”.

Select the range of cells to match and their replacement value

This is prompting you to select the range of cells containing the old values and the corresponding new value that will replace the old value.

In the above example, the range I entered says H4 to I7.

Click the “ok” button.

In the above screenshot, you can see the “To be updated” column now shows updated data.

This works when there are multiple values in a cell to be replaced as well.

Follow the same instructions to complete the find and replace.

Done!

Now, to address all the haters.

Yes, I realize XLOOKUP does this more easily.

No, I don’t have XLOOKUP on this computer.

Yes, I’m going to add it to this computer with these add-in instructions.

But I digress…

XLOOKUP Instructions for Find and Replace

In cell F2, I’d have written the following formula:

=XLOOKUP($E2,$H$4:$H$7,$I$4:$I$7,$E2)

Let’s break apart this formula to understand what it’s doing.

• Blue: E2, This is what I want to change
• Red: H4 to H7, This is the range of “old values” I want to compare the data in cell E2 to
• Purple: I4 to I7, This is the range of “new value” I want to replace the old value with
• The last blue value in the formula, cell E2, is saying “if you don’t find a corresponding value in the New Value range, leave the cell’s current value as is