Find replacements in Excel how to use

find replacements in Excel how to use

One, shortcuts

Ctrl+f Open the Find and Replace dialog box

Ctrl+h opens the Find and Replace dialog box.

Second, look for the scope

The lookup range has: Global and local lookups.

If the lookup is on the entire worksheet, click any cell to find it. If it is a local lookup, first determine the lookup range, such as only in column A, you can select column A, and then open the Find and Replace dialog box.

A column-by-row lookup refers to the priority.

If the cursor is currently in cell A1 and the A1,a2,b1 cell contains the data you want to find, the lookup result B1 first and A2.

In the Find and Replace dialog box, click the Options button, and in the range Drop-down list, you can select workbooks and worksheets as needed.

Third, the use of wildcard characters

Advanced Fuzzy Lookup can be achieved by combining the wildcard character "* and?".

Use the asterisk "*" in Excel find and replace to find any string, such as finding "it*" to find "it master" and "It Forum".

Use the question mark to find any single character. For example, find "23" to find "023" and "423" and so on.

In addition, if you look for wildcard characters themselves, you can enter "~*", "~?". "~" is the wave number, on the left of the number key "1". If you are looking for "~", enter two wave number "~ ~".

Iv. using Format Search

For example, change the data in the worksheet to red to blue, and show it in bold.

Click the small arrow to the right of the format button in the Find and Replace dialog box, select Format from cell in the Drop-down list that pops up, and then select a cell that contains the desired lookup format to find in the selected format. Then click Format with Replace with to format the cell.

V, press the SHIFT key to find the opposite direction

When you click the Find Next button in the Find and Replace dialog box, Excel looks in one direction. If you hold down the SHIFT key before you click the Find Next button, Excel looks in the opposite direction to the original lookup direction.

Six, close the Find and Replace dialog box and continue to find the next

In the lookup process, sometimes the Find and Replace dialog box obscures some of the table contents. You can also continue to find the next item after you close the Find and Replace dialog box by first looking, then closing the Find and Replace dialog box, and pressing the shortcut key SHIFT+F4 to continue to find the next one.

Find case

You can specify that Excel finds only special text with some uppercase format. For example, you can look for "green" instead of looking for "green". When looking, tick case sensitive.

Find in formulas, values, annotation ranges

Excel provides a lookup of formulas, values, and annotations.

Annotation lookup is easier to understand, and here's an example of a formula and a value:

There is a formula in the A1 =1+2

1, according to the formula to find 1, then find the content of the formula, not the value, so can find.

2, according to the formula to find 3, when 3 is the formula to show the result, that is, the value, so can not find.

3, by value to find 1, when 1 is the content of the formula, not the value, so can not find.

4, by value to find 3, when 3 is the value of the formula, so you can find.

Nine, use the Find Replace and AutoCorrect features

For example, in Excel input data, need "Graduation School" column, there are many students graduated from the same school, repeat input efficiency is certainly not high. You can customize a few characters temporarily replace the name of each school (such as "4Z" on behalf of "Beijing Four secondary School", and so on, and then press "Ctrl+h" all the "4Z" to replace all the "Beijing Four secondary schools" can be.

You can take advantage of the AutoCorrect feature in Excel if you want to omit the ctrl+h step. With the AutoCorrect feature, you can simplify the user's input to a large number of duplicate data, such as correcting the "4z" definition above to "Beijing Four middle School", which is defined by:

1, bring up the "AutoCorrect" dialog box.

2, in the "Replace" box to enter data, such as: "4z", in the "Replace with" box input data, such as: "Beijing Four Middle School", click "Add", and click "OK."

After this setting, simply enter the word "4z" in the cell, and it will automatically be corrected as "Beijing four secondary School".