How to Find and Replace within Selection in Excel (7 Methods)

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.


Download Practice Workbook


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.

Introduction to the Feature

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.

Introduction to the Feature

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.

Introduction to the Feature

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.

Introduction to the Feature

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.

Keyboard Shortcut of the Excel Find and Replace Feature

Moreover, press CTRL + H to open the Replace feature.

Keyboard Shortcut of the Excel Find and Replace Feature


7 Methods to Find and Replace within Selection in Excel 

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.

Dataset

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.

Excel Find and Replace within Selection Selected Cells Only

Secondly, move to the Replace tab (or press CTRL + H). And then, type Steve Smith.

Excel Find and Replace within Selection Selected Cells Only

After clicking on the Replace option, you’ll see the string is Steve Smith instead of only Smith.

Excel Find and Replace within Selection Selected Cells Only

Similarly, you might accomplish Find and Replace Multiple Values if you want.

Read More: How to Find and Replace Multiple Words at Once in Excel (7 Methods)


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.

Using Wildcards

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.

Using Wildcards

➦ After clicking on the Replace All option, you’ll get the following message which displays that Excel replaced 2 replacements.

Using Wildcards

➦ Finally, you’ll get the following output where the two cells are replaced with the James string.

Using Wildcards

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.

Excel Find and Replace within Selection Add or Remove Line Break

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.

Excel Find and Replace within Selection Add or Remove Line Break

Shortly, you’ll get the following output without any line breaks.

Excel Find and Replace within Selection Add or Remove Line Break

Read More: Add Text and Formula in the Same Cell in Excel (4 Examples)


Similar Readings:


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.

Excel Find and Replace within Selection Changing 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.

Excel Find and Replace within Selection Changing Formatting

Then, pick the format by clicking over a cell containing existing formatting.

Excel Find and Replace within Selection Changing Formatting

Again, click on the second Format option, and choose the Format option.

Excel Find and Replace within Selection Changing Formatting

Then, specify the blue color from the Fill tab.

Excel Find and Replace within Selection Changing Formatting

After pressing OK, you’ll get the following previews, and click on the Replace All option.

Excel Find and Replace within Selection Changing Formatting

Eventually, you’ll get the changed formatting as depicted in the following screenshot.

Excel Find and Replace within Selection Changing Formatting

Related Content: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)


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.

Changing Cell Reference

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.

Excel Find and Replace within Selection Changing Cell Reference

After clicking on the Replace option, you’ll get your desired output.

Excel Find and Replace within Selection Changing Cell Reference

Read More: How to Replace Text in Excel Formula (7 Easy Ways)


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.

Excel Find and Replace within Selection Across the Entire Workbook

If you click on the Replace All option, Excel will show a message of accomplishing 4 replacements along the entire workbook.

Excel Find and Replace within Selection Across the Entire Workbook

For example, if you move to another worksheet within the same workbook, you’ll get the changed output.

Output of the Entire Workbook

Related Content: How to Find and Replace Values in Multiple Excel Files (3 Methods)


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.

How to Insert VBA Code

Secondly, go to Insert > Module.

How to Insert VBA Code


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

VBA Find Function

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.

Using the VBA Code


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

VBA Replace Function

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.

Using the VBA Code

Note: Here, I discussed the basic VBA Find and Replace function. You may explore 11 ways to Find and Replace using the VBA.

Read More: Excel VBA: How to Find and Replace Text in Word Document


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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo