asked on

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

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

lee555J5

Convert them to upper case before testing equality, as in

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

Lee

ASKER CERTIFIED SOLUTION

NULL_ReferenceException

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lee555J5

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

nutsch

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