Find and Replace Using Multiple Values

Find and Replace Using Multiple Values

  • Post author:
  • Post category:Find

Find and replace is an excel feature used to search for specific data in an excel worksheet or within a workbook and what you do with the data found. This article will explore different ways you can use find and replace together with using advanced find features.

Scanning through columns and rows in a large excel document to look for certain data may be difficult but using the “excel find and replace” feature, you can easily find any data you need within a big excel sheet in seconds.

 

Using the Excel Find in a range of cells

The find tool can help you look for certain information in your worksheet. To do this, select a range of cells to look in.

Go to the home tab > Editing group > click Find & Select > click Find. Alternatively, you can press CTRL+F keyboard shortcut.


In the Find what box, type the characters you want to look for and click Find All or Find Next


Find All opens all the occurrence of the typed character and you can navigate to the corresponding cell by clicking any item in the list.

Find Next when you click this option, Excel selects the first occurrence of the search value on the sheet and when you click again it selects the second occurrence on the cell. This goes on until the last item is searched.

 

Using Find and Replace tool

The find and replace changes the value of one cell to another within a range of cells in a worksheet. The replace tab can change characters, texts and numbers in excel cells.

Steps

1. Select the range of cells where you want to replace the text or numbers.

2. Go to Home menu > editing ground > select Find & Select > Click Replace or press CTRL+H from the keyboard


3. On Find what box type the text or value you want to search for. In the Replace with box, type the text or value you want to replace with.


4. Click Replace button to replace a single text or click Replace All to replace the entire sheet with that value or text.

 

How to Find and Replace Multiple Values at once with VBA Code

Find and Replace can be used to find multiple values and replace them with values you desire using Excel VBA code.

Steps

1. Create the conditions that you need to use. This should be made of a list of old value and replace values.


2. Click on Developer tab then select Visual basic under code group or hold down ALT+F11 function key to open Visual Basic window.

3. Click Insert > module, and paste the following code in the module window.

Sub MultiFindNReplace()

‘Update 20140722

Dim Rng As Range

Dim InputRng As Range, ReplaceRng As Range

xTitleId = “KutoolsforExcel”

Set InputRng = Application.Selection

Set InputRng = Application.InputBox(“Original Range “, xTitleId, InputRng.Address, Type:=8)

Set ReplaceRng = Application.InputBox(“Replace Range :”, xTitleId, Type:=8)

Application.ScreenUpdating = False

For Each Rng In ReplaceRng.Columns(1).Cells

InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value

Next

Application.ScreenUpdating = True

End Sub


4. Click Run or press F5 Key to run this code. Specify the data range in the pop-up window.


5. Click OK and another prompt dialog box will appear for you to select the criteria you have created in step 1.


6. Then click ok. From the below screenshot, you can see all the values have been replaced with the new values.


Using Excel REPLACE Function

The REPLACE function in Excel allows you to find certain characters or a single character in a text string and change it with a different set of characters.

Syntax

REPLACE (old_text, start_num, num_chars, new_text)

Function arguments

  1. Old_text – the original text (or a reference to a cell with the original text) in which you want to replace some characters.
  2. Start_num – the position of the first character within old_text that you want to replace.
  3. Num_chars – the number of characters you want to replace.
  4. New_text – the replacement text.

Example: We want to replace the chef position in cell B22 to be a cook


Click ok.