I am sure that as an Excel user
you must have had many occasions when you had to check whether some text,
number or format is available in a sheet or the workbook. Sometimes you might
have to replace all instances of existing text with new text. Excel’s Find and Replace feature is available
to specifically facilitate this. In this post, we will see some details about
this feature and few cool tricks .
What is?
This is a feature in Excel to
facilitate finding of cells with specific text, formula, format or comment etc.
It is also possible to select any, few or all of such cells. Additionally, you
can replace any text in the cell with any other text you want.
How to access?
Excel 2007 or later: Home > Editing Group > Find
& Select > Find
Excel 2003 or earlier: Edit > Find
In all the versions, key board
shortcut Ctrl+F shows up the same
dialogue box.
Let’s see in details various
options available in this feature.
Find Tab
This tab can be used when you
only need to find text or numbers.
Find what box – You can enter text or numbers that you want to
search for (this box is also a dropdown list that contains all the recently
searched items, so you can even select from this list)
Options >> - You can further refine / expand your search by
clicking on this button which on clicking shows few more options to work with. You will notice that Find what box size is reduced and few more options are available.
Clicking Format… shows up another dialogue box
(similar to Format Cells box but
here it is named as Find Format. You
can define the criteria based on number style, alignment, font, border, cell
colour or protection etc. Upon clicking on OK in this box, Formats you selected
will be shown in Find and Replace box
to the left of Format button.
Format… button also has a dropdown
button at the end which has few more options.
Format… in the dropdown menu will show
the Find Format dialogue box
Choose Format From Cell… will let you
select a cell so that it’s format will be considered for search
Clear Find Format lets you clear off
any format you already selected
Within box: To search for data in a worksheet select Sheet or
select Workbook to search in entire
workbook
Search box: To search for data in rows or columns, click By Rows or
By Columns.
Look in box: Select appropriate option to search for data in
Formulas, Values, or Comments.
Match case check box: To search for case-sensitive data, select it
Match entire cell contents check box: To search for cells that
contain just the characters that you typed in the Find what box, select this.
Find All: Lists down all the occurrences of text / format entered in
Find what box.
Find Next: Find and selects the cell that contains the next
occurrence of text / format entered in Find
what box.
Close: Closes the Find and
Replace box without any further action.
Replace Tab
This tab can be used when you
need to replace text or numbers. All
features discussed above will stay with following additional boxes.
Replace with: This box is similar to Find what, and you need to define what you want to replace with.
Format button: See above.
Replace All: Replaces all the occurrence of text / format in Find what with text / format in
Replace with.
Replace: Replaces the first occurrence of text / format in Find what with text / format in
Replace with. This selects next
available occurrence.
That’s the theory. Now let’s get
into the cool part to see few practical uses of this.
Tip 1: Find in Only Particular Region
Select the region you want before
accessing Find and Replace feature.
If more than one cell is selected, Find
and Replace checks only in the selected part. Select any one cell if you
want Excel to find in entire sheet.
Tip 2: Select All Occurrences
Find and Replace feature can list down all the occurrences of text, number or format you are looking for. However, sometimes you might want to
select all the cells that contain a specific format so that you can change that format for all the cells in one shot.
You can use Find and Replace to do
this for you. Once you selected the format you are looking for, click Find All and Excel will list all the
cells in the dialogue box. Press Ctrl+A
to select all of referenced cells in the list. Click Close. Now, your cells are selected.
You can also keep pressing Ctrl and click individual entries with mouse to select only few cells of your choice.
You can also keep pressing Ctrl and click individual entries with mouse to select only few cells of your choice.
Tip 3: Use Wildcards
Find and Replace supports entry of wildcards. You can use ? (question mark) to search for a
single character and * (asterisk)
for any number of characters. For example: if you type b?ll in Find what then
Excel will search for all words that has b
in the start and ending with ll like
bill, ball, bell etc. This feature is of particular use if you know the number
of characters & any character of what you are looking for.
Using asterisk will give you more
flexibility. Example: if you search for b*t,
Excel will look for all the words that has b in the start and t in
the end like bat, best, bolt, bright etc.
Tip 4: Search for Asterisk or Question Mark
In our earlier tip, we saw that
you can use ? and * as wildcards. But what if you want to search for them
specifically? Every time you use these characters in Find what box, Excel will think you are
using them as wildcards. In this case, you should place a ~ (tilde) before ? or *. This will tell Excel that any character that follows tilde will have
to be looked for as it is.
Happy Learning!!
Good content and a nice blog. Thanks for sharing such good content. Keep sharing content with us. You can try our software Excel Search and Replace.
ReplyDelete