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.
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:
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