Md. Abdul Kader
Frequently, you may need to find a particular data, formatting, or even cell references and then replace it with something new. Find and Replace is one of the simple but multifaceted features that may fulfill your requirement. In this article, I’ll demonstrate to you 7 methods to find and replace in Excel within the selection with the necessary explanation.
Introduction to the Excel Find and Replace Feature
The Find and Replace feature provides the advantage of searching any data and then modifying or removing the data with the new one. You may simply open the feature from the Editing ribbon in the Home tab. Then, click on the Find & Select option. Immediately, you’ll see some options e.g. Find, Replace, and so on.
To open the Find feature, click on the corresponding (Find) option.
Shortly, you’ll see the following dialog box. Under the Find tab, you’ll see a box namely Find What. In the box, you have to type the text or numbers or any data that you want to find out. After doing that, if you click on the Find All option, Excel will show you all matched occurrences with the name Workbook, Sheet, Cell, Value, etc. But if you click on the Find Next option, you’ll see the first matched occurrences instead of the occurrences.
Besides, you may easily open the Replace feature by either choosing the Replace tab from the above picture or Replace option from the Find & Select option.
After opening the following dialog box, you’ll see a box after Replace with. Here, you need to insert the data that you want to replace. Then, if you choose the Replace option, you’ll see that only a single data is replaced. But in the case of the Replace All option, all data will be replaced within seconds.
Keyboard Shortcut of the Find and Replace Feature
Alternatively, you may use the keyboard shortcut if you wish. Just press CTRL + F to open the Find feature.
Moreover, press CTRL + H to open the Replace feature.
How to Find and Replace within Selection in Excel: 7 Methods
Let’s introduce today’s dataset where the sales report of some Items is given along with the concerning Sales Rep. Besides, Quantity, Unit Price, and Sales for each item are also provided.
Now, you’ll see the application of the Find and Replace feature.
1. Find and Replace in Selected Cells Only
In the beginning method, you’ll learn how to find and then replace a selected cell. For example, you may require to find and replace the value of the B7 cell having the spring namely Smith. Follow the steps below.
Firstly, go to the Find what box of the Find feature and type the string as shown in the following image. If you click on the Find All option, you’ll see the cell location of the cell is $B$7.
Secondly, move to the Replace tab (or press CTRL + H). And then, type Steve Smith.
After clicking on the Replace option, you’ll see the string is Steve Smith instead of only Smith.
Similarly, you might accomplish Find and Replace Multiple Values if you want.
2. Using Wildcard to Find and Replace within Selection
Sometimes, you may need to modify or delete data containing some specific values. In such a situation, you may utilize the wildcards to find and replace within the selection. Here, I am going to show you the application of the Asterisk (*) wildcard.
Assuming that you want to find the texts containing “lia”. This may be located in the middle of a text.
➦ Firstly, you may find the texts containing special characters (“lia”). For finding this, just type the *lia* in the Find what box. Then click on the Find All option.
Immediately, you’ll get that $B$8 and $B$12 cells have the characters.
Note: To find the text that begins with “lia”, you have to insert lia*. But you need to insert *lia if you want to find the text that ends with “lia”.
➦ Next, you need to specify the text that you want to get as the replaced text. If you want to get the string James for the texts containing “lia” characters, just type the James in the box of Replace with and also keep the Find what box as it remains.
➦ After clicking on the Replace All option, you’ll get the following message which displays that Excel replaced 2 replacements.
➦ Finally, you’ll get the following output where the two cells are replaced with the James string.
Whatever, I skipped the use of Question mark (?) as wildcards to find and replace.
Read More: How to Find And Replace Values Using Wildcards in Excel
3. Add or Remove a Line Break Using the Find and Replace Feature
In addition, you may have tons of line breaks in your dataset and you want to remove them. It is quite tedious to delete the line break manually. Luckily, you may remove the line breaks using the Find and Replace feature within seconds.
In the following image, I have added a single line break (adding their product model name) for the C5 and C6 cells. Now, I want to remove the line breaks rapidly.
To execute the task, you have to press CTRL + J (the keyboard shortcut for a line break based on ASCII code) in the Find what box. And insert comma (,) and space characters in the box of the Replace with option.
Shortly, you’ll get the following output without any line breaks.
Similar Readings:
- Find and Replace a Text in a Range with Excel VBA
- [Fixed!] Excel Find and Replace Not Working
- How to Find and Replace Multiple Values in Excel with VBA
- How to Find and Replace from List with Macro in Excel
4. Changing Specific Formatting
Furthermore, you might change the formatting of a cell or multiple cells using the Find and Replace feature within the selection in Excel.
Let’s say, you have the following formatting (yellow-colored) in some cells. But you want to change the yellow-colored formatting to blue-colored formatting.
For changing the formatting, click on the drop-down list of the Format option in the Find and Replace dialog box, and click on the Choose Format from Cell.
Then, pick the format by clicking over a cell containing existing formatting.
Again, click on the second Format option, and choose the Format option.
Then, specify the blue color from the Fill tab.
After pressing OK, you’ll get the following previews, and click on the Replace All option.
Eventually, you’ll get the changed formatting as depicted in the following screenshot.
5. Changing Cell Reference
Moreover, you can change the cell reference if necessary.
The used formula in the H5 cell returns the output (Sales) for the corresponding Sales Rep.
Clearly, the same formula would not work for the below cells if you copy the formula unless you change the cell reference from $G$5 into $G$6.
Surely, you can do this in two ways. Either you can remove the absolute reference before the column number or use the Find and Replace feature. Just insert the $G$5 in Find what box and $G$6 in Replace with box.
After clicking on the Replace option, you’ll get your desired output.
6. Find and Replace across the Entire Workbook within Selection
More importantly, you might find and replace the selected data for all the working sheets of any workbook.
For example, I want to find David and then replace it with David Bowie. To accomplish the task, insert David in the Find what box and David Bowie in the box of the Replace with option.
Then, choose Workbook instead of Sheet from the drop-down list of the Within option.
If you click on the Replace All option, Excel will show a message of accomplishing 4 replacements along the entire workbook.
For example, if you move to another worksheet within the same workbook, you’ll get the changed output.
Related Content: How to Find and Replace Values in Multiple Excel Files
7. Using the VBA Code
If you are accustomed to using VBA code in Excel, you may use the Find and Replace here also. However, you must insert a module to enter the VBA code.
Firstly, open a module by clicking Developer > Visual Basic.
Secondly, go to Insert > Module.
7.1. Find using VBA
Then, copy the following code into the newly created module to find the string i.e. David.
Sub Find()
Dim Rng As Range
Set Rng = Sheets("Find&Replace_VBA").UsedRange.Find("David")
MsgBox Rng.Address
End Sub
Here, I declared Rng as Range. Then, I set Rng with the VBA Find function (also specified the string “David” and the UsedRange property to find the specified string. Besides, I used the Sheets function along with the current sheet name.
Next, if you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll see the following output.
7.2. Replace Using VBA
Again, use the following code to replace the specified string.
Sub Replace()
Sheets("Find&Replace_VBA").UsedRange.Replace What:="David", Replacement:="Steve"
End Sub
In the above code, I used the VBA Replace function. Then, I specified “David” after the What option that I want to remove and “Steve” after the Replacement option that I want to get as the replaced string.
After running the code, you’ll get the following output.
Note: Here, I discussed the basic VBA Find and Replace function. You may explore other ways to Find and Replace using the VBA.
Read More: Excel VBA: How to Find and Replace Text in Word Document
Download Practice Workbook
Conclusion
That’s the end of today’s session. I strongly believe from now you may execute the find and replace within selection in Excel. Anyway, if you have any queries or recommendations, please share them in the comments section below
Related Articles
- How to Find and Replace Asterisk (*) Character in Excel
- How to Find and Replace in Excel Column
- How to Find and Replace Using Formula in Excel
- Find and Replace Tab Character in Excel
- Find And Replace Multiple Values in Excel
- How to Find and Replace Multiple Words from a List in Excel
- Excel VBA to Find and Replace Text in a column
