How to search/find and replace values in selection or selected cells in Excel?
In Excel, the ability to quickly find and replace values within a specific selection or range of cells is essential for data cleaning, updating records, or correcting errors across large datasets. While Excel offers built-in tools to perform simple find and replace actions, users may sometimes need more advanced solutions - such as handling only a part of the data, ignoring cases, or performing replacements without directly overwriting the original values. This article introduces several practical methods, ranging from Excel's default features to more advanced tools and automation, to help you efficiently search or locate and replace data within your selected cell ranges.
- Search or find and replace values in selection with Find and Replace feature
- Search or find and replace values in selection, across sheets, or in entire workbooks with Kutools for Excel
- Find and replace values using Excel formula
- Replace multiple different values in selection at once using VBA
Search or find and replace values in selection with Find and Replace feature
Excel’s built-in Find and Replace feature allows users to quickly search for specific text, numbers, or characters within a selected range and, if necessary, replace those values with new ones instantly. This approach is ideal when you want an immediate, straightforward way to update data within a targeted set of cells or ranges, and do not need advanced options like custom criteria or formula-based replacements.
Applicable scenarios: Use this method for quick edits where you need immediate, bulk replacements across a handpicked range. This is best for straightforward tasks that do not involve advanced search rules or partial matches only.
Limitations: This tool is less suitable when you require greater control, such as replacing values only if cells contain exact content, or working within a strict case-sensitive environment.
1. Select the range or cells where you want to search or replace values.
2. Press Ctrl + F to open the Find and Replace dialog box.
3. To locate a specific value, click the Find tab, enter the value in the "Find what" field, and click Find All. Excel will list all matching results at the bottom of the dialog box, along with clickable links so you can jump directly to any result. See example screenshot below:
 | >>> |  |
If you want to replace a value, switch to the Replace tab. Enter the text to find in the "Find what" box and your replacement value in the "Replace with" box. You can choose Replace (for the current cell) or Replace All (to update all occurrences in your chosen selection).
After clicking the Replace All button, Excel will display a summary dialog informing you how many cells were replaced. Click OK to close the prompt. See screenshot below:
Tips: If you only want to replace whole cells (not partial matches), click Options in the dialog box and check the "Match entire cell contents" box for more precise replacements. For case-sensitive finds, enable "Match case".
Error reminder: Always confirm you have the correct selection before running Replace All, since this action cannot be easily undone if applied to a large range.
Search or find and replace values in selection, across sheets, or in entire workbooks with Kutools for Excel
The Advanced Find and Replace pane from Kutools for Excel expands on Excel's standard capabilities, letting you search or replace values not only within specific selection(s) or cells, but also across all open workbooks, the current active workbook, or even across multiple worksheets simultaneously. This is highly useful in scenarios where your data spans several worksheets or workbooks, and you wish to avoid repeated manual operations.
1. Once you install Kutools and launch Excel, the Kutools Navigation Pane typically opens on the left side.
Tips: If you do not see this pane, open it by choosing Kutools > Navigation from the ribbon.
2. In the Find and Replace panel, you can customize your operation:
button to activate the Find and Replace pane.
to pinpoint one or more exact ranges for your operation.
Replacement or search results are immediately displayed in the pane. Compared with built-in Find and Replace, Kutools provides more flexibility, especially when consolidating replacements across different worksheets.
Note: With this feature, you can also freely search or replace data not just in your current selection, but across all open workbooks, only the active sheet, multiple specific sheets, or your entire Excel environment, vastly reducing time spent on repetitive manual updates. See screenshot:
Advantages: This solution is ideal for users who regularly manage large workbooks, complex data layouts, or require quicker batch processing. Kutools further supports case selection, wildcard matches, and more refined options.
Precautions: Please review your settings carefully and select the correct scope to avoid unintended replacements beyond your target area.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Search or find and replace values in selection via Excel formula
If you need to perform a find and replace operation but prefer not to overwrite your original data - as when you wish to generate new values in another column while preserving the source data - Excel formulas such as SUBSTITUTE or REPLACE are extremely practical.
Applicable scenarios: When you need to keep the original data intact, perform partial replacements (e.g., within a string), or when you want the changes to update automatically if the source data changes.
Pros: Non-destructive - your original values remain unaltered. Formulas can be copied or filled down to process large ranges quickly. You can adapt the formula for more complex string manipulations.
Cons: Requires a new column for the formula output. Not suitable for global replacements in-place unless you later copy-paste values to overwrite originals.
Replace specific text using the SUBSTITUTE function:
1. Suppose your values are in column A. Click cell B1 (or the first cell next to your data) and enter the following formula:
=SUBSTITUTE(A1,"old_text","new_text")Parameter explanation: "old_text" is the value you want to find, and "new_text" is the value you wish to use as a replacement. Modify these values as needed.
2. Press Enter. Drag the fill handle down to apply the formula to all rows as needed. This will display the replaced values in column B, while column A remains unchanged.
Replace text at specific positions using the REPLACE function:
1. If you need to replace content at specific positions only, use the REPLACE formula as follows: In cell B1, enter the following formula:
=REPLACE(A1, start_num, num_chars, "replacement")Parameter explanation: start_num specifies the position where the replacement begins, num_chars is the number of characters to replace, and "replacement" is the new text to insert. Modify these values as needed.
2. Press Enter. Drag the fill handle down to apply the formula to all rows as needed.
Troubleshooting: If the formula does not produce the expected result, ensure cell references and replacement values are correct, and that your formula is entered in the adjacent column. If you overwrite the original data, you may lose information, so always double-check references before copying values back to the source.
Replace multiple different values in Excel at once using VBA
If you often need to replace several different words or values in Excel, doing it one by one can be time-consuming. With a simple VBA macro, you can replace multiple items in one go. Follow the steps below to create and use the Multi Find & Replace tool.
Pros: Highly flexible and fully automatable; can be tailored for unique batch-edit tasks.
Cons: Requires familiarity with VBA.
Step-by-step to create and run a VBA macro for customized find and replace:
1. Click Developer > Visual Basic (or press ALT + F11). In the Microsoft Visual Basic for Applications editor that opens, click Insert > Module to add a new module. Copy and paste the following code into the module:
Sub MultiFindReplace()
Dim WorkRng As Range
Dim ReplaceList As Variant
Dim cell As Range
Dim Pair As Variant
Dim ReplaceCount As Long
On Error Resume Next
Set WorkRng = Application.InputBox("Select the range to perform find and replace:", "Multi Find & Replace", Type:=8)
If WorkRng Is Nothing Then Exit Sub
On Error GoTo 0
ReplaceList = Array( _
Array("apple", "orange"), _
Array("dog", "cat"), _
Array("Ktools", "Kutools"), _
Array("2024", "2025") _
)
ReplaceCount = 0
For Each cell In WorkRng
If Not IsEmpty(cell.Value) Then
For Each Pair In ReplaceList
' Replace all instances of each "Find" with "Replace"
If InStr(1, cell.Value, Pair(0), vbTextCompare) > 0 Then
cell.Value = Replace(cell.Value, Pair(0), Pair(1), 1, -1, vbTextCompare)
ReplaceCount = ReplaceCount + 1
End If
Next Pair
End If
Next cell
MsgBox ReplaceCount & " replacements completed.", vbInformation, "Multi Find & Replace"
End Sub2. Inside the code, find the section below, where each line represents a Find → Replace pair. You can modify, remove, or add pairs as needed. Each pair follows the format Array("old_text", "new_text"), where the first value is what you want to find, and the second value is what you want to replace it with.
ReplaceList = Array( _
Array("apple", "orange"), _
Array("dog", "cat"), _
Array("Ktools", "Kutools"), _
Array("2024", "2025") _
)3. Click the 
Run button to execute the code. When prompted, select the range of cells where you want to perform replacements.
The macro will automatically go through each cell and replace all defined values. A message box will tell you how many replacements were made.
Practical tips: For recurring tasks, you can save the workbook as a macro-enabled file (.xlsm) to reuse this macro. If prompted with security warnings, enable macros to proceed.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in