Excel > Functions

Find vs Search function in Excel

What is the difference between Find and Search function in Excel and how to use them?

In previous post, we learnt about Text to Columns in Excel for CSV data. In this post, we shall learn the difference between FIND and SEARCH function in Excel and where to use what?

FIND function

Look at below example. In B2, we have '=FIND("JOHN", A2)' formula that returns '#VALUE' as FIND couldn't find the exact value ('JOHN') in A2 cell. (FIND is case-sensitive function).

FIND function in Excel

No support for wildcard characters

Look at below example, we are trying to find 'J?hn' where I am looking for any character in place of '?'. FIND gives '#VALUE' error.

Wildcard in FIND function of Excel

SEARCH function

When we pass the same parameters in SEARCH function, we get '1'. So our formula in C2 is '=SEARCH("JOHN", A2)'. As SEARCH function is case-insensitive, as long as text to find is same (either upper or lower or mixed case), it returns the position of the text found in A2 cell.

SEARCH function in Excel

See the similar examples below for FIND

FIND function in Excel


SEARCH function in Excel

Support for wildcard characters

SEARCH function supports wildchard characters, you can see that 'J?hn' parameter still in SEARCH still returns '7'.

Wildcard in SEARCH function of Excel

Quickies Wink

Can we also use '*' wildcard character instead of '?' - Yes

 Views: 319 | Post Order: 29

Write for us