How to make the FIND function in Excel ignore case sensitivity?

Say I have an Excel sheet of 100 records where there are 3 columns A, B and C.

The requirement is I have to get all the rows from column C if the text in column C matches with SQL Server.

Here the text SQL Server is in different formats SQL Server,sql Server,sql server

I used the command =IF(ISERROR(FIND("SQL Server", C2)), 1, 0) to find the matching rows in column C.

If a match is found it returns 0 otherwise it returns 1.

Now the problem is it is returning 1 for strings like sql server and sql Server.

How do I make the FIND command case insensitive?

user840963

Posted 2011-09-16T21:09:08.743

Reputation:

Answers

=IF(ISERROR(FIND("sql server",LOWER(C2))),1,0)

Silx

Posted 2011-09-16T21:09:08.743

Reputation:

Thanks Silx also I tried this way =IF(ISERROR(SEARCH("sql server",LOWER(C2))),1,0) is this correct way? I could see all the field values matching with sql server are resulting 0 irrespective of the case. – None – 2011-09-16T21:37:45.070

3FIND is case sensitive, SEARCH is not (and can use wild cards) so you could use =IF(ISERROR(SEARCH("SQL Server",C2)),1,0) – chris neilsen – 2011-09-16T22:16:32.040

Stumbled here looking for something else but the solution is buried so may as well post it here:

Use SEARCH instead of find. FIND and SEARCH are identical functions other than that SEARCH isn't case sensitive.

Some_Guy

Posted 2011-09-16T21:09:08.743

Reputation: 684