How to search text in Excel

When using Excel, we come across a lot of strange scenarios. One of those circumstances is looking for a certain text string within a cell. In many circumstances, you might want Excel to automatically locate and extract data from other cells depending on your criteria. Let's investigate the capabilities of the Excel search features in more detail.

FIND function in Excel to search text:

To find a specific character or substring inside a text string in Excel, utilise the FIND function.

The Excel Find function's syntax is as follows:

FIND(find_text, within_text, [start_num])

The first two parameters must be provided, while the third is optional.

1.Find_text - The character or part of the string you're looking for.

2.The text string to search within is designated aswithin_ text.  You can put the string directly into the formula, but often it is given as a cell reference.

3.Start_num is an optional input that indicates the character at which to start the search. If the within text string is missing, the search begins with the first character.

A #VALUE! error is given if the FIND function cannot locate the find text characters.

Since g is the fourth letter in the word king, the formula =FIND(g, king) produces 4, for instance. There is no a in king, therefore the formula =FIND(a, king) gives an error.

Important information about Excel's FIND function:

The following straightforward truths should be kept in mind while using an Excel FIND formula:

1.Case matters when using the FIND function. Use the SEARCH feature to find a match regardless of case.

2.Excel's FIND function does not support wildcard characters.

3.The location of the first character is returned by the FIND function if the find text parameter contains several characters. For instance, the word happy begins with the letter a, therefore the formula FIND(ap,happy) yields 2.

4.The first instance of find text is returned if within text has several instances. For instance, the first l character in the word hello is located at position 3, which is returned by FIND(l, hello) as an example.

5.The Excel FIND formula delivers the first character in the search string if find text is an empty string ().

6.If any of the following situations arise, the Excel FIND function produces the #VALUE! error:

 Within text does not contain the text find text.

More characters are in Start num than in within text.

Start num is a negative value or zero.

SEARCH function in Excel to search text:

Since it also gives the position of a substring within a text string, Excel's SEARCH function is quite similar to FIND in this regard. Are the parameters and syntax similar to those of FIND:

SEARCH(find_text, within_text, [start_num])

The SEARCH function, in contrast to FIND, is case-insensitive and supports the use of wildcard characters, as seen in the example below.

Here are a few straightforward Excel SEARCH formulas:

1.Because the substring coat starts at the fifth character of the word raincoat, =SEARCH(coat, raincoat) yields 5.

2.Because r is the first character in the word ring, disregarding case, =SEARCH(r, ring) yields 1.

3.Excel's SEARCH function returns the #VALUE, just like FIND does. error when:

 The find text argument's value could not be located.

The start num parameter is longer than the within text argument in length.

Start num is greater than 0 or equal to it.

Excel SEARCH to search text vs. Excel FIND to search text:

As was already established, the syntax and applications of Excel's FIND and SEARCH functions are quite similar. But they are dis similar in a few ways too.

Case sensitivity in FIND vs Case-sensitivity in SEARCH :

The primary distinction between the Excel SEARCH and FIND functions is that SEARCH ignores case whereas FIND does.

SEARCH(e, Excel), for instance, yields 1 because it doesn't care about the case of E, but FIND(e, Excel) returns 4 since it does.

Use wildcard characters while searching:

Excel's SEARCH function, in contrast to FIND, supports wildcard characters in the find text argument:

One character is denoted by a question mark (?) and any sequence of characters is denoted by an asterisk (*).

Consider the following illustration to see how it performs with actual data:

Did you learn about how to search text in Excel file? 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