How to use the FIND and REPLACE functions in Excel?

1. How to use the FIND function - Search in Excel tables.

Step 1:

First of all, open the Excel spreadsheet you need to work.At the Home tab on the Ribbon, we left-click the Find & Select item.Then continue to choose Find or press Ctrl + F.

How to use the FIND and REPLACE functions in Excel? Picture 1

Step 2:

Immediately after that, the interface of Find and Replace dialog box appears in Excel.Here, you will enter the word or data to search in the Find What box.For example, I want to find the location of the number 45.

How to use the FIND and REPLACE functions in Excel? Picture 2

Step 3:

In this next step, we will have two options to perform.

a) If you choose Find Next

This means that I will search for each metric one by one, the word requested, and the result will display the position of the number 45 in turn.

The first position of the number 45 will be in column B4 in the data table and be framed.

If we continue to click on Find next, the next position of the number 45 in column C6 as shown in the picture, is also framed for easy identification.

You continue to perform the above operation to find the remaining values ​​in the spreadsheet.

b) If you select Find all

This will aggregate all the locations from which the word we want to find and the statistics right below for you to know.

The result will be the two positions of the number 30 in Excel worksheet, in Book 1 and Sheet 1. When you click on a certain line, immediately there will appear position 30 in the table is also framed, for example. like B4.

How to use the FIND and REPLACE functions in Excel? Picture 3

2. How to use the REPLACE function - Replace in Excel.

Step 1:

We also open the working Excel file.In the Home tab, click Find & Select but select Replace, or press Ctrl + H.

How to use the FIND and REPLACE functions in Excel? Picture 4

Step 2:

The Find and Replace dialog box appears.You type the word to be replaced in the Find what box and type the replacement word in the Replace with box.

How to use the FIND and REPLACE functions in Excel? Picture 5

Step 3:

The replacement of words and figures in Excel also includes 2 different options.

a) If you select Replace

If the user needs to replace each location with different numbers then click this item.

As shown in the figure, I chose Replace to replace the two positions 45 in the table in B4 and C6 respectively.In position B4 has changed from 45 to 30.

How to use the FIND and REPLACE functions in Excel? Picture 6

You continue to follow the above operation to replace the numbers and other characters in turn.

b) If Replace All is selected

If we need to replace all 1 numeric values, characters with 1 number or another character then click this item.

Here, I will replace the number 45 with the number 30 at all positions in the data table and click Replace All.

The result will be the data table changed number 45 to 30 with two positions B4 and C6 as shown below.

How to use the FIND and REPLACE functions in Excel? Picture 7

Above is a detailed tutorial on performing search and replace operations on Excel spreadsheets.In this way, we will quickly find and replace values ​​with just a few simple steps.Besides, you can also choose to find or replace each value or the entire value in Excel data.

Go To Special - Find and Replace in Excel

You can use the Go To Special feature to quickly select all cell formulas, comments, conditional formatting, constants, data validation, etc. For example, to select all cells with formulas, perform the following steps. .

Select a cell.

On the Home tab, click Find & Select, Go To Special .

How to use the FIND and REPLACE functions in Excel? Picture 8

Note: Formulas, Comments, Conditional formatting, Constants and Data Validation are shortcuts.They can be found under 'Go To Special'.

Select 'Formulas' and click OK.

How to use the FIND and REPLACE functions in Excel? Picture 9

Note: you can search for cells whose formulas return numbers, strings / text, Logic (TRUE and FALSE) and errors.A check box is also available if you select Constants.

Excel selects all cells with formulas.

How to use the FIND and REPLACE functions in Excel? Picture 10

Note: if you select a cell before you click Find, Replace, or Go To Special, Excel will search the entire worksheet.To search in a data area, select that data area first.

The above are tips to help you use the Find and Replace function in Excel.