How to find text in a cell

The SEARCH Function finds text in a cell. The text's numerical location is returned if it is located. An error is issued if the text cannot be located. The ISNUMBER Function allows us to return TRUE or FALSE depending on whether the text was found.

The Excel TEXT functions section includes the FIND Function. The function will give back the location of a given character or substring inside of a text string that has been provided. We may use this function to identify particular data for financial analysis.

Example:

There are instances when you need to search within a cell for a certain passage of text. In one case I dealt with in my professional experience, a worksheet incorporated data from two distinct systems for a dummy incoice and record number. The invoice number was placed after the DI code on credit notes in one system and before it in the other. To determine which of the entries were dummy invoice, I had to devise a formula.

The LEFT, RIGHT, and IF functions may come to mind if you have prior Excel experience. These would provide a solution, but some very complicated formulas would be required. A much simpler approach is provided via the SEARCH feature.

The SEARCH function to find text in a cell:

The SEARCH function in Excel looks for text in a cell. There is no need to precisely remove the necessary portion using the LEFT, RIGHT, or MID operations because it scans the whole cell.

By using the following formula, you can look for the word DI in the text:

=SEARCH(“DI”,A2)

As you've seen, the SEARCH function looks for the word DI and returns a number indicating where in the cell it is located. You can see that the text DI is present in cells A2  at position 1, which is the cell's very first position. It is located at position 5 at the end of cell A4. A #VALUE! error is issued if the text cannot be retrieved at all.

ISNUMBER function to find text in a cell:

The results of ISNUMER are very self-explanatory: they return TRUE if the target includes a number and FALSE otherwise. This is what you get when you combine it with the preceding formula:

=ISNUMBER(SEARCH(“DI”,A2))

The outcome I was hoping for is this one. Now that I can tell which records were credit notes, I can sort, filter, and do computations using methods like SUMIF and COUNTIF.

FIND function to find text in a cell:

Before I wrap up this essay, I should also point out the related FIND function.

The FIND function functions essentially identically to SEARCH. The sole distinction is that FIND takes case into account.

Any of the following expressions would have produced the same results because SEARCH is not case sensitive:

=SEARCH(“DI”,A2)

=SEARCH(“DI”,A2)

=SEARCH(“DI”,A2)

=SEARCH(“DI”,A2)

If you capitalised FIND correctly, it would work:

=FIND(“DI”,A2)

Although SEARCH is more frequently used than FIND to find text in a cell, there may be times when you require the case sensitivity that FIND offers.

Did you learn about how to find text in a cell in Excel. You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.