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

Steve_Brady
Steve_Brady used Ask the Experts™
on
Hello,

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(LEFT(A1,2)=LEFT(A2,2),"YES","NO")

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?

Thanks
Comment
Watch Question

Do more with

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

Commented:
Convert them to upper case before testing equality, as in

=IF(UPPER(LEFT(A1,2))=UPPER(LEFT(A2,2)),"YES","NO")

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

=IF(EXACT(LEFT(A1,2),LEFT(A2,2))=FALSE,"NO","YES")

Commented:
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

=IF(EXACT(LEFT(A1,2),LEFT(A2,2)),"YES","NO")

be simpler?

Lee
Top Expert 2008

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

=EXACT(LEFT(A1,2),LEFT(A2,2))

Thomas

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