How to Replace Words in Excel?
When working with massive Excel data, it can be difficult and time-consuming to locate specific information or data.
In Excel, you can use the Find and Replace features to search for something in your workbooks, such as a particular number or text string. You can either locate the search item for reference or replace it with something else.
You can include wildcard characters such as question marks, tildes, asterisks, or numbers in your search terms. You can search by rows and columns, search within comments or values, and search within worksheets or entire workbooks.
How to use Find Feature
The following steps show you how to find specific characters, text, numbers, or dates in a range of cells, worksheets, or the entire workbook.
Consider an example in which we use the Find command to locate a specific department in this list.
Step 1: Select the range of cells where you want to replace text or numbers. To replace characters across the entire worksheet, click any cell on the active sheet.
Step 2: Go to the Home tab and click on the Find & Select button in the ribbon’s Editing group.
Or you can open the Excel Find and Replace dialog by pressing the Ctrl + F shortcut key.
Step 3: The Find and Replace dialog box will appear.
Step 4: In the Find what box, type the characters (text or number) you are looking for.
Step 5: Click either Find All or Find Next. If the content is found, then you will select the cell containing that content.
When you click Find Next, Excel selects the first occurrence of the search value on the sheet; the second click selects the second occurrence, and so on.
When you click Find All, Excel opens a list of all the occurrences, and you can click any item in the list to navigate to the corresponding cell.
Step 6: When you are finished, click Close to exit the Find and Replace dialog box.
Additional Options in Find Dialog Box
To fine-tune your search, click Options in the right-hand corner of the Excel Find & Replace dialog. It allows doing the following tasks, such as:
- To search for the specified value in the current worksheet or entire workbook, select Sheetor Workbook in the Within
- To search from the active cell from left to right (row-by-row), select By Rowsin the Search To search from top to bottom (column-by-column), select By Columns.
- To search among specific data types, select Formulas, Values, or Commentsin the Look in
- For a case-sensitive search, check the Match case check.
- To search for cells that contain only the characters you’ve entered in the Find whatfield, select the Match entire cell contents.
NOTE: If you want to find a given value in a range, column, or row, select that range, columns, or rows before opening Find and Replace in Excel. For example, limit your search to a specific column, select that column first, and then open the Find and Replace dialog.
How to Use Replace Feature
At times, you may discover that you’ve repeatedly made a mistake throughout your workbook, such as misspelling someone’s name or that you need to exchange a particular word or phrase for another. You can use Excel’s Find and Replace feature to make quick revisions.
To replace certain characters, texts, or numbers in an Excel sheet, use the Replace tab of the Excel Find & Replace dialog. You need to follow the following steps:
Step 1: Select the range of cells where you want to replace text or numbers. To replace characters across the entire worksheet, click any cell on the active sheet.
Step 2: Go to the Home tab, click on the Find & Select button and select the Replace feature.
Or Press the Ctrl + H shortcut key to open the Replace tab of the Excel Find and Replace dialog.
If you’ve just used the Excel Find feature, then switch to the Replace tab.
Step 3: In the Find what box, write the value to search for. And in the Replace with box, type the value to replace with.
Step 4: Now, click either Replace to replace the found occurrences one by one or Replace All to swap all the entries in one fell swoop.
Step 5: When you are finished, click Close to exit the Find and Replace dialog box.
NOTE: If something has gone wrong and you got the result different from what you’d expected, click the Undo button or press Ctrl + Z to restore the original values.
Replace Data with Nothing
To replace all occurrences of a specific value with nothing, type the characters to search for in the Find what box, leave the Replace with box blank, and click the Replace All button.
How to Find or Replace a Line Break in Excel
To replace a line break with space or any other separator, enter the line break character in the Find what filed by pressing Ctrl + J. This shortcut is the ASCII control code for character 10 (line break or line feed).
After pressing Ctrl + J, the Find what box will look empty at first sight, but upon a closer look, you will notice a tiny flickering dot like in the screenshot below. Enter the replacement character in the Replace with box, e.g., a space character, and click Replace All.
To replace some character with a line break, do the opposite – enter the current character in the Find what box and the line break (Ctrl + J) in Replace with.
Excel Find and Replace with Wildcards
The use of wildcard characters in your search criteria can automate many finds and replace tasks in Excel:
- Use the asterisk(*) to find any string of characters. For example, Ro* finds “Robert” and “Ronnie“.
- Use the question mark(?) to find any single character. For example, ?ose finds “Rose” and “Jose“.
For example, to get a list of first names that begin with “Ro“, use “Ro*” for the search criteria. Also, with the default options, Excel will search for the criteria anywhere in a cell.
In our worksheet, it would return all the cells that have “Ro” in any position. To prevent this from happening, click the Options button and check the Match entire cell contents box. This will force Excel to return only the values beginning with “ad“, as shown in the below screenshot.
If you need to find actual asterisks or question marks in your Excel worksheet, type the tilde character (~) before them.
For example, to find cells that contain asterisks, you would type ~* in the Find what box. To find cells that have question marks, use ~? as your search criteria.
As you can see below, Excel successfully finds and replaces wildcards both in text and numeric values.
How to change cell formatting on the sheet
Excel Replace allows you to take a step further and change all cells’ formatting on the sheet or in the entire workbook. You have to follow the following steps:
Step 1: Click on the Find and Select button, and select the Replace feature.
Step 2: And click the Options button.
Step 3: Click on the Format button.
And specify the formatting you want to find or replace. Click the Ok button.
Or
Next to the Find what box, click on the arrow of the Format button.
Step 4: Select Choose Format From Cell and click on any cell with the format you want to change.
Step 5: Next to the Replace with box, either click the Format button and set the new format using the Excel Replace Format dialog box, or click the arrow of the Format button, select Choose Format From Cell, and click on any cell with the desired format.
Suppose you want to replace the formatting on the entire workbook, select workbook in the Within box. If you’re going to replace formatting on the active sheet only, leave the default selection.
Step 6: Finally, click the Replace All button and verify the result.
As a result, it replaced the entire first-row background with the selected red color font background.
NOTE: This method changes the formats applied manually. It will not work for conditionally formatted cells.
Advanced Find and Replace Features
In Excel, Advance Find and Replace are used to find and replace in all open workbooks, add-in by Ablebits. The following Advanced Find and Replace features make a search in Excel even more powerful, such as:
- Find and Replace in all open workbooksor selected workbooks & worksheets.
- Simultaneous search in values, formulas, hyperlinks, and comments.
- We are exporting search results to a new workbook.
Follows the following steps to run the Advanced Find and Replace add-in in Excel:
Step 1: Click on the Excel ribbon icon, which resides on the Ablebits Tools tab.
Step 2: Go to the Search group and click the Find and Replace button. You can also press Ctrl + Alt + F or even configure it to open by the familiar Ctrl + F shortcut.
The Advanced Find and Replace pane will open, and you do the following:
- Type the characters (text or number) to search for in the Find what
- Select which workbooks and worksheets you want to search. By default, all sheets in all open workbooks are selected.
- Choose what data types to look in: values, formulas, comments, or hyperlinks. By default, all data types are selected.
- Select the Match caseoption to look for case-sensitive data.
- Select the Entire cell check box to search for an exact and complete match, i.e., find cells that contain only the characters you’ve typed in the Find what
Step 3: Click the Find All button, and you will see a list of found entries on the search results tab.
Step 4: And now, you can replace all or selected occurrences with some other value or export the found cells, rows, or columns to a new workbook.
Step 5: Click on the Replace All button.
Step 6: Click on the Export all button or select according to your need.
As a result, it creates a result workbook, as shown below.
Replace Function in Excel
The REPLACE function in Excel allows you to swap one or several characters in a text string with another character or a set of characters. Below is the basic syntax of Replace function.
The Excel REPLACE function has four arguments, all of which are required.
- Old_text: The original text or a reference to a cell with the original text in which you want to replace some characters.
- Start_num: The position of the first character within old_text that you want to replace.
- Num_chars: The number of characters you want to replace.
- New_text: The replacement text.
NOTE: If the start_num or num_chars argument is negative or non-numeric, an Excel Replace formula returns the error (#VALUE!).
