Excel Magic Trick 520: FIND & SEARCH Functions

Learn about FIND and SEARCH functions:
1.Both functions find the position of a substring in a string the position of some characters within a different set of characters. For example FIND(A,ISAAC) = 3, SEARCH(A,ISAAC) = 3 because the first A is the third character in ISAAC.
2.FIND is case sensitive and does not allow wildcards such as * (1 or more characters) or ? a single character.
3.SEARCH is NOT case sensitive and it accepts wildcards.
4.Use LEFT FIND and SERCH to extract first names
5.See how to do Fuzzy Math or Approximate Lookup using LOOKUP, SEARCH, FIND, 2^15

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• Difference
• Between
• FIND
• SEARCH
• functions
• function
• Fuzzy
• Match
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[AutoBahnForever]

Is it possible to capture results of a "Find All" for a specific word and transfer it to a new Excel spreadsheet ?

[ExcelIsFun]

@AutoBahnForever , I don't know. Try:

mrexcel [dot] com/forum

[Lucream2]

Hi Mr ExcellsFun I would like to use the find and replace functions to replace data in the same cells from different sheets of the same documents. I don´t want to use the Ctrl "F" function I want a formula to do this for me. Please help me I enjoy your vedios a lot. Thanks in advance.

[ExcelIsFun]

I do not know. I think you would need VBA to do that. Try;

mrexcel[dot]com/forum

[theyangist]

Is there a way to search for any matches from a list of strings inside of groups of text?

In other words, is there a way to pick an array for x in SEARCH(x,y,z), or is there a better method overall for doing it?

I have some lexicographical information that I need to match in two separate arrays.

[ExcelIsFun]

Try this video:

Excel Magic Trick 323: Partial Text VLOOKUP (Fuzzy Match)

There is no way to do this perfectly becasue you are doing a partial lookup. You also might try posting a detailed question to THE best excel question site:

mrexcel[dot]com/forum

[ExcelIsFun]

I don't understand your question.

[udanial]

mike hi!

ur videos are awesome,i wanted to ask dat y find didnt give correct answer without adding 1 can u explain a bit?

[ExcelIsFun]

I agree: Houdini is an amazing Exceler!!

[chinchonchinchon]

At first I didn't have a clue what that 2^15 meant--Now I do...

Of course, you'll never find such a big text string ever in an excel cell, I think so I've tried 1000 and naturally it worked. Houdini is such a clever fellow.