The last one is optional.

Find text in excel sheet formula. The FIND formula is used to return the position of a substring or special character within a text string. 1 Select Statistical from the Formula Type drop-down list. How to Search Text in Excel.

When you click OK Excel will format cells with errors on your active sheet in gray. With some preliminary setup you can use this approach to search an entire workbook for a specific value. In the example shown the formula in D5 is.

Copy the formula to the rest of the cells using the Ctrl D or dragging it down from the right bottom tiny box of the applied cell. The first two arguments are mandatory. In the opening Formula Helper dialog box please do as follows.

When the text is not found FIND returns a VALUE error. If all that seems a little crazy you can also the COUNTIF function to find text. The following formula is quite similar to the formula above except that it is a regular formula and it has an additional INDEX function.

You can also use SEARCH and FIND functions in combination to find the text substring from a given string. You can use the following formula. 1048576 C4 Now COUNTIF will count cells with the substring John anywhere in the cell.

Lets look at a few methods. First prepare an excel sheet with the required details in it. If you want to count all cells that contain the value in C4 instead of all cells equal to C4 add the wildcards to the criteria like this.