Is it possible to make a command such as =LEFT(), case specific in Excel?

Steve_Brady used Ask the Experts™

In Excel (2007), is it possible to make a command such as =LEFT(), case specific?

For example, suppose you are evaluating the first two characters in A1 & A2 with the following formula in A3:


If A1 & A2 contain the following:

          A1:   October
          A2:   Octopus
          A3:   =IF(LEFT(A1,2)=LEFT(A2,2),"YES","NO")

then A3 will return:  YES.  Similarly, if the entry in A2 is changed as shown here:

          A1:   October
          A2:   octopus
          A3:   =IF(LEFT(A1,2)=LEFT(A2,2),"YES","NO")

then A3 will still return:  YES

Is there any way to modify the formula in A3 so that it will detect the different case of the letter "O" and as a result, return "NO" in the second example?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Convert them to upper case before testing equality, as in


If you want to check case, use the EXACT function to compare strings.  Like this:


You're right, NULL. I read that wrong. I read the Asker wanted to ignore case. UPPER would force that, but Excel does that anyway.

Steve_Brady, EXACT is the function you want; so NULL's answer is correct, mine is not.

NULL, why do you reverse the test and the True/False parts? Would not


be simpler?

Top Expert 2008

And if you can be happy with a TRUE / False result instead, you can drop the if altogether.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial