Search for specific text/FIND, SEARCH

=FIND(find_text, within_text, [start_num])

[find_text] = Identify text to search for.
[within_text] = Identify search range.
[start_num] = Not obligatory. Select location of letter (character) implying the beginning of search. If nothing is written here, it will search from the 1st letter.

Definition of function A function to search for a specified text in a specified range, and if found, identifies the number of characters/letters to the 1st character of [find_text].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=SEARCH(find_text, within_text, [start_num])

[find_text] = Identify text to search for.
[within_text] = Identify search range.
[start_num] = Not obligatory. Select location of letter (character) implying the beginning of search. If nothing is written here, it will search from the 1st letter.

Definition of function function to search for a specified text in a specified range, and if found, identifies the number of characters/letters to the 1st character of [find_text].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) FIND・SEARCH function

The table below shows the book codes at a local book store. The code has ’12 characters’ and ends with ‘○○~BOOKS’.
To check that all of the codes end correctly with ‘BOOKS’, we will use the FIND function and the SEARCH function.

① Use the FIND function to search for text.

(1) [find_text] : Surround the word ‘BOOKS’ with ” (double quotations). → “BOOKS”
(2) [within_text] : Select cell range ‘B5’.
(1) [start_num] : We do not write anything here.
=FIND(“BOOKS”,B5)

① Use the SEARCH function to search for text.

(1) [find_text] : Surround the word ‘BOOKS’ with ” (double quotations). → “BOOKS”
(2) [within_text] : Select cell range ‘B5’.
(1) [start_num] : We do not write anything here.
=SEARCH(“BOOKS”,B5)

The number displayed tells us the number of characters to the 1st letter of ‘BOOKS’ (i.e. the ‘B’ in ‘BOOKS’ is the 8th character counting from the left).
If the word is not found, the error ‘#VALUE’ is displayed, like in cell D7 and E7.

The meaning to ‘#VALUE!’

It is a type of error when the content of the formula is incorrect.
There is no ‘BOOKS’ in cell B7 as there is mistake, so Excel displayed the error to check if ‘the formula was incorrect’.

 

POINT!

The FIND function and the SEARCH function have the same content in their formula.
However, the results they display are different when the codes are incorrect.

We un-capitalized ‘BOOKS’ in the codes only for B8.
Also, for cell E5, we used ‘*’ (wildcard character) to change the formula to search for ‘B*’ (words that start with B).

The difference between the FIND function and the SEARCH function

  • The FIND function distinguishes ‘capital/non capital words’.
  • With the SEARCH function we can use wildcard characters and set up ‘ambiguous searches’.

 

Count the length of text

=LEN(text)

[text] = Identify text to count length.

Definition of function A function that counts the number of characters (letters) in a text.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) Count the number of characters/LEN function

We will now check if there are ’12 characters’ in each code listed below by counting the number of characters in column D.

① Count the number of characters using the LEN function.

(1) [text] : Select cell to count the number of characters, cell ‘B4’.
=LEN(B4)

 

Count cells with ‘texts’

The table below is the attendance list for a group of universities that have been invited to an Excel research meeting. The ‘Attendance’ table shows the number of participants if attending, and a ‘×’ for no attendance.
We will calculate the number of universities not attending.

 

How to count ‘text’ cells) The COUNT, COUNTA function

① Use the COUNT function and the COUNTA function to count only the cells with ‘text’.

The COUNT・COUNTA function is…

「COUNT」 → A function to count the number of cells of a number
「COUNTA」 → A function to count the number of all full cells   For more information over the functions, click here

(1) Use the COUNTA function to count the number of all full cells in cell range [C5:C9]. → 「COUNTA(C5:C9)」
(2) Use the COUNT function to count the number of cells with numbers in cell range [C5:C9]. → 「COUNT(C5:C9)」
(3) Subtract the results of the COUNT function from that of the COUNTA function.

=COUNTA(C5:C9)-COUNT(C5:C9)

 

Compare to see if 2 texts are the same

=EXACT(text1, text2)

[text1] = Identify 1st text to compare.
[text2] = Identify 2nd text to compare.

Definition of function A function that compares 2 texts, and if same, displays a ‘TRUE’ and if not, a ‘FALSE’.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) Compare 2 texts/EXACT function

The table below shows the ‘Company name’ and ‘E-mail address’ of companies that attended an event. To avoid mistakes, we added the ‘E-mail address check’ and asked them to type the same address again. We will use the EXACT function to see if there are no mistakes in those addresses by comparing column C and D, and displaying the results in column E.

① Check for e-mail address mistakes using the EXACT function.

(1) [text1] : Select 1st text to search for, cell [C4].
(1) [text2] : Select 2nd text to search for, cell [D4].
=EXACT(C4,D4)