Excel function of the week - FIND and SEARCH

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

This week we will look at some text functions. FIND and SEARCH are essentially the same function with only minor differences.

magnifying-glass-778751

These functions both allow you to search through a text string (think sentence or word or phrase) in a cell and return the number of the starting character. So when cell A1 has The quick brown fox, =FIND(“fox”,A1) would return 17.

The following screenshots indicate the function arguments and explain the functions syntax.

imageimage

The main difference between FIND and SEARCH is that FIND is case sensitive while SEARCH is not. Search also allows the use of wildcards when searching.

 

I use FIND a lot when trimming and cutting through text strings to look for certain value or words. It is very useful when massaging system generated reports into a database of rows and columns.

The Excel help topic for both FIND and SEARCH has this little example that is also useful:

Use start_num to skip a specified number of characters. Using FIND as an example, suppose you are working with the text string "AYF0093.YoungMensApparel". To find the number of the first "Y" in the descriptive part of the text string, set start_num equal to 8 so that the serial-number portion of the text is not searched. FIND begins with character 8, finds find_text at the next character, and returns the number 9. FIND always returns the number of characters from the start of within_text, counting the characters you skip if start_num is greater than 1.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Anonymous's picture

I really like the find and

I really like the find and search functions in excell and all the office products. Excell is great because you can search by column or row or sheet. It's such a powerful program.