Find & Replace | Find

Find

Find is an option in Excel with the help of which we are going to find anything in the data sheet/worksheet. 
Find is a search function inbuilt in Excel.

One simply needs to specify the search criterion. Excel will do the rest.

It is located in 'Find & Select' function under Home tab.
Short Cut 'Ctrl+F.'

Previous post's data sheet is used for demonstration.

Find & Replace

Find Window

How to find? 
Say we want to find 'Smith'. 
Type Smith in 'Find what' text box. 
If you press Find Next, you can view cells one at a time where content of the cell is Smith. In fact it is not the view, the cursor goes on making each Smith's cell active when you press Find Next.

If you press 'Find All' a separate window pops below where number of find can be seen with respective cell names.
And if you click each row in the window the cursor will move to the respective location in the sheet.

Results of Find

3 Cells found. 
Now where the results are located?
In the SalesOrders(2) sheet of Filter.xlsx file. 
And the respective cell numbers are C15, C21, C24.


Links has been created for each find. Cursor transforms into 'hand' when we hover the mouse cursor above each entry. If we click any entry excel makes the respective cell active in the corresponding data sheet. 

Here excel ignores upper case and lower case sensitivity.
Since we put 'smith' for search criterion but the contents are 'Smith'.


But interestingly excel accepts any blank space into consideration.
Say if we put smith with a blank space after h (by pressing space bar) excel will fail to find any. Since no cell contained with Smith has any blank space after h. Of course if any content of cell with smith is with a blank space after h, excel will find that cell.

Again excel can find any blank space 'only'. We put a blank space in the Find What text box (by pressing space bar once), and we know a cell with Smith has a blank space after h.
Excel can find that cell along with other cells where there are blank spaces.
Excel can find special characters. Say #, @,. etc

Even excel can find digits or alphabets in any content. Say we put 2 for search, and a date 10-12-2019 is there. Excel will take into consideration this date along with other content say 72,727 etc.

Again if we put '-2' for search, excel will find this date only with other dates where search criterion matches.

You can also use wild card characters like *,? for search criterion.
Type p*n to find string of characters say pen & pencil. 
Again type c*l to find central and pencil.

Use question mark ? to find a single character.
Put p?n to find pen. 
Put 4?9 to find 449.10.

Now if we want to find ? or * what will we put as a search criteria?
Put ~ (tilde) just above the tab key in keyboard.
~? or ~* to find ? and * respectively.

Advanced find options

Click 'Options>>' in the  Find & Replace window.

Advanced Option

In advanced option One can specify the sheet or workbook to search, by clicking at 'Within' combo box. 

One can specify the pattern of search say by row or column wise. If column wise is specified excel first reads the first column then the next and so on. In row wise search excel search rows one after another.

Lookin combo box specifies whether to search formulas also including text. And if one specifies text in the combo it ignores formulas. Say 2 is multiplied with the content of the cell G1 and the formula thereof would be G1*2. Now if we search G and specifies 'text' in the drop down combo it ignores the G in the formula cells. It may be handy. 

Now one can search contents of comments by specifying 'comments' in the look in combo box. Where in the search ignores all cell that are not commented.

Another important aspect of Find is, one can specify the area of the search in the worksheet. Say we need to search a telephone number within a column of telephone numbers. We can select the column only and search. Search results will be limited to the column only.

Match Case:

As it has been said that excel ignores uppercase and lowercase in case of ordinary search. But if we tick the check box associated with Match Case, excel will be very specific about its search. Excel will make the difference between SMITH & smith. If we search for 'smith' it won't find 'SMITH'.

Match Entire Cell contents:

If we tick the check box associated with 'Match Entire cell contents' excel will again be very specific about its search. In the ordinary scenario if we want to search for 'Binder' we can put the search criteria as 'bind'. We the box is ticked it won't find 'Binder' with search criteria 'bind'. It will consider those cells where entire search criteria matches.

Find Format:

We can search and find a particular format in the worksheet or workbook. Say we want to find all the 'bold' characters in the data sheet. 

Find Format

• Click 'Format' (as above).

• Under 'Find Format' dialogue box click Font Tab. 

• Under Font Style click or highlight Bold. 

• Click Ok.

• 'Find What' text box should remain empty because we are not searching any particular text or digit. We are simply searching the bold characters and digits in the sheet.
• Click 'Find All'. 
• Even you can search for colored cell, font style, italics, underlined etc. in this manner.