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.