EXCEL FUNCTION-SEARCH

INTRODUCTION

SEARCH FUNCTION is one of the very important and useful functions in Excel.

As SEARCH  FUNCTION is concerned with the text, it is present under the TEXT FUNCTION CATEGORY of the functions.

SEARCH FUNCTION FINDS THE POSITION OF A CHARACTER OR ANY TEXT FRAGMENT INSIDE ANOTHER TEXT AND RETURNS THE STARTING CHARACTER LOCATION AS THE POSITION.

SEARCH FUNCTION counts the characters whether it is single byte or double byte. [Byte is the size of the characters]

SEARCH FUNCTION is quite useful in manipulating text to get the desired results easily.

In this article we would learn about the purpose, syntax, formula of the SEARCH FUNCTION and get a better understanding with the help of the examples.

PURPOSE OF SEARCH FUNCTION IN EXCEL

https://googleads.g.doubleclick.net/pagead/ads?us_privacy=1—&client=ca-pub-3894096195292173&output=html&h=280&adk=3943266357&adf=2878511904&pi=t.aa~a.1618001517~rp.1&w=996&fwrn=4&fwrnh=100&lmt=1617990133&rafmt=1&to=qs&pwprc=2196269336&psa=1&format=996×280&url=https%3A%2F%2Fgyankosh.net%2Fmsexcel%2Ffunctions%2Fexcel-text-functions%2Fexcel-function-search%2F%23gsc.tab%3D0&flash=0&fwr=0&pra=3&rpe=1&resp_fmts=3&wgl=1&fa=40&dt=1617990113452&bpp=5&bdt=4130&idt=6&shv=r20210406&cbv=r20190131&ptt=9&saldr=aa&abxe=1&cookie=ID%3Db41a5cb189e66f3a-22460afed7c40041%3AT%3D1605880192%3ART%3D1605880192%3AR%3AS%3DALNI_MYRs0eKq0LSuIv_AMQC2BxdrJqY6g&prev_fmts=0x0%2C255x600&nras=2&correlator=621260868671&frm=20&pv=1&ga_vid=117138297.1592477652&ga_sid=1617990113&ga_hid=555735629&ga_fc=0&u_tz=330&u_his=1&u_java=0&u_h=936&u_w=1664&u_ah=895&u_aw=1664&u_cd=24&u_nplug=0&u_nmime=0&adx=356&ady=1331&biw=1648&bih=765&scr_x=0&scr_y=0&eid=44740079%2C44739387&oid=3&pvsid=3782866255779983&pem=612&ref=https%3A%2F%2Fgyankosh.net%2Fmsexcel%2F&eae=0&fc=1920&brdim=-8%2C-8%2C-8%2C-8%2C1664%2C0%2C1691%2C917%2C1664%2C765&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&ifi=3&uci=a!3&btvi=1&fsb=1&xpc=1sKRg5qDSB&p=https%3A//gyankosh.net&dtd=20439

SEARCH FUNCTION is used to search any character or text fragment  within a longer text and return its starting number.

For example,

Suppose we need to find out the location of any character or text pattern in the given text.

let us take a text snippet ” Have a wonderful day!“

Now if we want to find the location of the first ‘a’ or the word ‘wonderful’ we can do so with the use of SEARCH FUNCTION.

The requirements can be of numerous types such as finding out any character or word before we replace it or extract it.
https://googleads.g.doubleclick.net/pagead/ads?us_privacy=1—&client=ca-pub-3894096195292173&output=html&h=280&adk=4002320043&adf=2687390412&pi=t.aa~a.131391915~rp.4&w=480&fwrn=4&fwrnh=100&lmt=1617990133&rafmt=1&to=qs&pwprc=2196269336&psa=1&format=480×280&url=https%3A%2F%2Fgyankosh.net%2Fmsexcel%2Ffunctions%2Fexcel-text-functions%2Fexcel-function-search%2F%23gsc.tab%3D0&flash=0&fwr=0&pra=3&rpe=1&resp_fmts=3&wgl=1&fa=40&dt=1617990113486&bpp=5&bdt=4164&idt=6&shv=r20210406&cbv=r20190131&ptt=9&saldr=aa&abxe=1&cookie=ID%3Db41a5cb189e66f3a-22460afed7c40041%3AT%3D1605880192%3ART%3D1605880192%3AR%3AS%3DALNI_MYRs0eKq0LSuIv_AMQC2BxdrJqY6g&prev_fmts=0x0%2C255x600%2C996x280&nras=3&correlator=621260868671&frm=20&pv=1&ga_vid=117138297.1592477652&ga_sid=1617990113&ga_hid=555735629&ga_fc=0&u_tz=330&u_his=1&u_java=0&u_h=936&u_w=1664&u_ah=895&u_aw=1664&u_cd=24&u_nplug=0&u_nmime=0&adx=854&ady=1636&biw=1648&bih=765&scr_x=0&scr_y=0&eid=44740079%2C44739387&oid=3&pvsid=3782866255779983&pem=612&ref=https%3A%2F%2Fgyankosh.net%2Fmsexcel%2F&eae=0&fc=1920&brdim=-8%2C-8%2C-8%2C-8%2C1664%2C0%2C1691%2C917%2C1664%2C765&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&ifi=4&uci=a!4&btvi=2&fsb=1&xpc=9I3JMPaCkm&p=https%3A//gyankosh.net&dtd=20451

PREREQUISITES TO LEARN SEARCH FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  • Some information about the TEXT HANDLING IN EXCEL is an advantage for the use of such formulas.
  •  Of course, Excel software.

Helpful links for the prerequisites mentioned aboveWhat Excel does? How to use formula in Excel?https://googleads.g.doubleclick.net/pagead/ads?us_privacy=1—&client=ca-pub-3894096195292173&output=html&h=280&adk=2845639811&adf=3134233332&pi=t.aa~a.2466986641~rp.4&w=470&fwrn=4&fwrnh=100&lmt=1617990133&rafmt=1&to=qs&pwprc=2196269336&psa=1&format=470×280&url=https%3A%2F%2Fgyankosh.net%2Fmsexcel%2Ffunctions%2Fexcel-text-functions%2Fexcel-function-search%2F%23gsc.tab%3D0&flash=0&fwr=0&pra=3&rpe=1&resp_fmts=3&wgl=1&fa=40&dt=1617990113522&bpp=7&bdt=4200&idt=7&shv=r20210406&cbv=r20190131&ptt=9&saldr=aa&abxe=1&cookie=ID%3Db41a5cb189e66f3a-22460afed7c40041%3AT%3D1605880192%3ART%3D1605880192%3AR%3AS%3DALNI_MYRs0eKq0LSuIv_AMQC2BxdrJqY6g&prev_fmts=0x0%2C255x600%2C996x280%2C480x280&nras=4&correlator=621260868671&frm=20&pv=1&ga_vid=117138297.1592477652&ga_sid=1617990113&ga_hid=555735629&ga_fc=0&u_tz=330&u_his=1&u_java=0&u_h=936&u_w=1664&u_ah=895&u_aw=1664&u_cd=24&u_nplug=0&u_nmime=0&adx=854&ady=2364&biw=1648&bih=765&scr_x=0&scr_y=0&eid=44740079%2C44739387&oid=3&pvsid=3782866255779983&pem=612&ref=https%3A%2F%2Fgyankosh.net%2Fmsexcel%2F&eae=0&fc=1920&brdim=-8%2C-8%2C-8%2C-8%2C1664%2C0%2C1691%2C917%2C1664%2C765&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&ifi=5&uci=a!5&btvi=3&fsb=1&xpc=rdBJQiFtd2&p=https%3A//gyankosh.net&dtd=20443

SYNTAX: SEARCH FUNCTION

The Syntax for the SEARCH function is
=SEARCH ( TEXT TO BE FIND, TEXT IN WHICH THE TEXT IS TO BE FOUND, STARTING CHARACTER POSITION FOR THE SEARCH)

TEXT TO BE FIND  is the character, text, word or pattern which we want to find

TEXT IN WHICH THE TEXT IS TO BE FOUND is the longer text in which we want the first argument to be found.

STARTING CHARACTER POSITION FOR THE SEARCH is the starting position from where we’d start our search. All characters before this position will be ignored. SEARCH FUNCTION IS NOT CASE SENSITIVE. IT’LL MATCH ONLY THE CONTENT AND NOT THE CASE.


 Suppose we want to find ‘ap’ in the word ‘Happy’. Then

TEXT TO BE SEARCHED  is “ap”

TEXT IN WHICH THE TEXT IS TO BE FOUND is “Happy”

STARTING CHARACTER POSITION FOR THE SEARCH is 1 as we want to start the search from the first letter.

EXAMPLES:SEARCH FUNCTION IN EXCEL

EXAMPLE TYPE 1: SEARCHING A LETTER [FIRST OCCURRENCE]

Let us take a group of words say “GOOD MORNING”.

Let us try to find out the position of ‘G’, space, ‘I’ and o.

STEPS TO FIND OUT THE POSITION OF THE CHARACTER IN THE GIVEN WORDS:

  • Select the cell where we want the result.
  • Enter the following formulas for G , space and I in the given word.
  • For finding the G, put the formula as =SEARCH(“G”,E6,1)
  • For finding the ” ” SPACE, put the formula as =SEARCH(” “,E7,1)
  • For finding the I, put the formula as =SEARCH(“I”,E8,1)
  • For finding the o, put the formula as =SEARCH(“o”,E8,1) . The answer was still returned as it ignored the case.
SEARCHING A LETTER IN A WORD USING SEARCH FUNCTION

EXAMPLE TYPE 2: SEARCHING A TEXT PATTERN IN THE GIVEN TEXT

Let us take a sentence ” Humpty Dumpty sat on a wall”

Let us try to find out the pattern ‘pty’ in the given sentence.

STEPS TO FIND OUT THE POSITION OF THE TEXT PATTERN IN THE GIVEN SENTENCE:

  • Select the cell where we want the result.
  • Enter the following formulas for finding “pty”.
  • For finding the text pattern “pty”, put the formula as =FIND(“pty”,E20,1)
  • Click Enter.
  • The result would appear.
  • The picture below shows the result and other details.
SEARCHING THE PATTERN USING SEARCH FUNCTION

EXPLANATION:

Let us understand the working of the formula.

We used the formula =FIND(“pty”,E20,1)

Where the first argument “pty” is the one which we want to find.

E20 contains the complete sentence in which we want to find the occurrence position of this pattern

Third argument is 1 which tells us that we want to start the search from the position 1.

Now, we might be thinking about the situation where there are two occurrences of “pty” in the sentence. What if we need the location of second occurrence.

Well, there is no direct function for finding out the second occurrence but we can use a trick to find one.

The next example is the extension to this one and finds out the second occurrence of “pty”

EXAMPLE TYPE 3: SEARCHING the TEXT pattern at the second position

Let us take a sentence ” Humpty Dumpty sat on a wall”

Let us try to find out the second occurrence of the pattern ‘pty’ in the given sentence.

STEPS TO FIND OUT THE POSITION OF THE SECOND OCCURRENCE OF THE PATTERN IN THE GIVEN SENTENCE:

  • Select the cell where we want the result.
  • Enter the following formulas for finding the second occurrence of the pattern “pty”.
  • For finding the second occurrence of “pty” , put the formula as =FIND(“pty”,E29,FIND(“pty”,E29,1)+1)
  • Click Enter.
  • The result would appear.
  • The picture below shows the result and other details.
SEARCHING THE SECOND OCCURRENCE OF PATTERN USING SEARCH FUNCTION

EXPLANATION:

Let us understand the working of the formula.

We used the formula

=FIND(“pty”,E29,FIND(“pty”,E29,1)+1)

In the previous example, we found the “pty” text pattern simply and started the search from the letter 1, which returned us the starting position of first occurrence of “pty” text pattern.

But in this case , we want to find the second occurrence. For that we planned to start the search from the position , after the first occurrence of the text pattern.In the formula, the first argument is the text which we want to find.

The second argument is the complete text in which we want to search for the text pattern.

The third argument, which is the starting position of the search is positioned by adding one to the first occurrence of the text pattern “pty”.

The portion of the function as the third argument FIND(“pty”,E29,1) returns the position of the first “pty” in the given text. Now if we start the search after this position,it’ll return us the second occurrence. That is the concept behind the formula .

KNOWLEDGE BYTES

DIFFERENCE BETWEEN FIND FUNCTION AND SEARCH FUNCTION

SEARCH FUNCTION also helps to search for a particular character, or patter or text within another longer text.

The only difference between the FIND FUNCTION and SEARCH FUNCTIONS is that the

FIND FUNCTION IS CASE SENSITIVE and SEARCH FUNCTION IS NOT CASE SENSITIVE. 

For example.If we want to find “ARE” in a sentence say ” How are you today” using both the functions.

We’ll find the following results.

FIND:

=FIND(“ARE”,”How are you today”,1).

The result will be a VALUE ERROR as it won’t be able to find our ARE as the sentence contains “ARE” and not “are”.

SEARCH:

=SEARCH(“ARE”,”How are you today”,1). The result will be 5 as “are” starts at the position 5. It’ll ignore the case and match “are” with  “ARE”.

DIFFERENCE BETWEEN SEARCH AND FIND FUNCTION