In this article, we will
discuss about the search methods in 'Excel FIND & REPLACE'. The FIND &
REPLACE option in Excel can do lot of things not only to FIND and then (if necessary) REPLACE but also you can search your ‘search items’ by their color, cell formatting,
you can even use wild characters like ? or *.
The shortcut is Ctrl+F to
open the FIND window and Ctrl+H to open the FIND & REPLACE window, the
title of both the window is FIND & REPLACE.
You can also get this window
from menus in the ribbon, you need to click in the Home tab, under Editing you
will see an icon of Magnifying Glass, click in this icon, a drop-down menu will
open and you will find these two options, FIND and REPLACE.
 |
| Find and Replace from Menu |
 |
| Find and Replace from Menu |
You will get the window of FIND
& REPLACE as below.
 |
| Find and Replace Window |
How to use FIND &
REPLACE ?
As described above use
either shortcut or from the menu click get the window FIND & REPLACE, if
you want to find some item, type the item in the input box named as ‘Find what:’,
press enter or click Find Next button.
As soon as you click the Find Next button,
the search term if available will be selected i.e., the cell containing this
search term will be selected, if not available a warning window will appear saying
item not found.
 |
| Error Message when Search Term not found |
To replace a string or
any other character we use the Replace tab in the Find and Replace window, you can get this option by the using shortcut Ctrl+H or from menu or by clicking
the Replace tab available in the FIND and REPLACE window. It displays two inputs
boxes, ‘Find what:’ & ‘Replace with:’
In the first input box
type that you want to replace, in the second input box type the replacement of
it, now to replace one by one click Replace button and to replace all the
matching text or characters at a time click Replace All button.
We all know these methods
very well, I hope, but in the next paragraphs you will find the real power of FIND
& REPLACE option in Excel.
The OPTIONS Button in the FIND & REPLACE window :
Advanced Search
You might notice that
there is an Options button above the Close button. If you click this button, the
FIND & REPLACE window will expand and will show you few more options as
shown in the below picture.
 |
| Options Button in Find and Replace |
The available options are
:
v Match Case : Check (tick) this option if you want to find the
cells that matches with the search term provided, it might be a part of the
cell content.
v Match entire cell content : Check this option to find
the cell that has exactly same data as provided by the search term
v Within : Drop down menu : By default Workbook is
selected, if you want to search in the current Sheet where you are working, select from
the drop down menu.
v Search : Drop down menu : Here you implicitly
instructs Excel to search by columns or rows, we rarely use this option, for
very large and big data we may use it to reduce the search time.
v Formulas : Drop down menu : This is most important part
because it relates to FORMULAS. There are three options in this drop down menu,
Formulas, Values & Comments.
Ø Formulas : We use simple to complex formula in Excel
to process data, sometimes we need to find or replace part of formula. If you
select this option and try to search for example, $D - which is generally a part
of formula the cell containing $D will be selected. Now you can change your
formula easily using this option. For example, suppose we need to replace the
$D by $E in a sum function as =SUM($D:$G), it can be replaced using this
option.
Ø Values : The FIND will work on the value of
the cell ignoring what formula is there in the backend.
Sometimes you may be surprised why you cannot find
the search string using FIND and Replace option although the data is existing.
This is one of the reasons behind the not getting your search term although exists, therefore, always
check what option is selected in the Search option, if you are searching for a
value and Search option is set as Formulas, this will not work.
Ø Comments : If you want to find something from the
comment section in the Excel, this option must be selected. I hope you know
what is a comment in Excel, I will discuss it latter. A cell is marked in red in a corner of a cell if any comment is present and normally the is hidden, it displays when we hoover over that red triangle.
v Format button (in the right upper corner) : You will
find Format button in Find option but two Format button in Find &
Replace option, this option is used to find the data based upon the formatting of the
cell. Therefore, you can guess that if you click on this button a new
window will appear, the well-known Format window, only the name if Find Format.
Select any format that you want to search by the format already formatted.
Let us have some examples
with pictures to make things clear.
How to use Find and Replace ?
Press Ctrl+F, type your
search term, click on Find Next button.
 |
| How to use Find and Replace |
How to use
Find and Replace to find all the occurrences
of given Search term ?
Same as the above process,
only you need to click on the Find All button and a list of occurrences will appear
in the below list, if more than one row is visible click on the link that cell
will be selected. In the below example two times the name is coming Donald
Trump and two lines are visible in the box highlighted by Blue Color.
 |
| Multiple Search Result in Find and Replace |
How to Find Data or String using Cell Color or Format ?
Click on the Options
button, now click on the Format button which will open another window Find Format,
select the format as per your search format, for example, the cell color is
yellow containing the name Donald Trump, so I select the yellow color from the
Fill option in the Find Format window and clicked Find Next, the cell is
highlighted as shown below.
 |
| Search by Format |
Note : Find by color will
take more time than normal search.
 |
| Search by Cell Color or Format |
When you want to replace your string you can follow the same method the only difference is that you must use Replace window by pressing Ctrl+H and use your replacement text or data. The rest is same, so I will not discuss it here, if you find problem please comment on the COMMENT box, I will clarify.
How to Search using Wild Card Character ?
There are two wildcard
characters available ‘?’ and ‘*’.
v ‘?’ is used for
any single character, For example, sm?th finds "smith" and
"smyth"
v ‘*’ is used for
any number of characters, For example, *east finds "Northeast" and
"Southeast"
 |
| WildCard Search |
Now the obvious question is how to search for these ‘?’
and ‘*’ wildcard characters?
Lets’ discuss about it.
Search for Wild Card Characters
Did you ever try to find ‘?’ or ‘*’ the Wild Card characters’
using Find and Replace ? If yes, then you have noticed that it gives nothing !!!
It just moves selected cells by one; like you are pressing Enter key in Excel.
As you know these (‘?’ or ‘*’) are
wild card characters, you can use these characters to find ‘partially known’
search terms.
How to search for Wild Card
Characters ?
To search for ‘?’ or ‘*’ you must use a tiled (~)
before these characters as shown in the below picture; when tiled sign is used
before ‘?’ or ‘*’, Excel treats
these as normal characters.
 |
| Search for Wildcard Characters |
Hope you have enjoyed this
article.
Thanks for reading…
