Find & Replace - Search By Color Format and Wildcard Search

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…