Find & Replace | Replace

Replace

Replace is an important function in Excel.
May be it is convenient to alter the cell content where the number of cell is less, but when data is huge Replace function excels.

One can alter/change/remove the content of cells by use of Replace function.

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

Easy and simple format just 'Find What' and to 'Replace With'. 

Replace is an intelligent function to remove or delete any particular content of cells from the data sheet. 

Say we want to remove or delete the text 'Binder' from the datasheet. 
Just type 'Binder' in 'Find What' text box, and keep 'Replace with' text box blank. Press the button 'Replace All', it will just replace or delete all text 'Binder' from the data sheet and keep the cells blank as we didn't specify anything to fill in. 
Now the problem may arise that it will delete or replace the text 'Binder' from all cells but where there may be a text called 'Hard Binder' it will remove text 'Binder' and text 'Hard' will only be left in the cell. 

To overcome this problem

• Click the 'Options<<' button.
• Tick the box 'Match entire cell content'.

Now the replace function will ignore the text 'Hard Binder'.

Replace Format

It is an intelligent option to change the format of cells.

Say we want to make 'Binder' text into 'Bold' in all the cells where the content is 'Binder'.

• Press Ctrl+F (Find & Replace Dialogue box pops up).
• Activate Replace Tab.
• Click 'Options > >' button.
• Write 'Binder' in both the textbox (Find What & Replace With) since we are going to replace 'Binder' with 'Binder' just with different format.
• Click 'Format' button relative to 'Replace with' (Extreme Left).
• In the 'Replace Format' dialogue box.
• Activate 'Font Tab', highlight 'Bold' under Font style.
• Again Activate 'Fill' tab.
• Click 'Fill Effects'.
• Under Shading Style highlight any Variant.
• Click Ok

• Click 'Replace All'. 

All the 'Binder' text will be replaced with a special format you chosen.Under preview button one see the format that will be incorporated.
One can clear the selected format by clicking 'Format' button and Click 'Clear Format' option.

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.