Link to home
Start Free TrialLog in
Avatar of Steve_Brady
Steve_BradyFlag for United States of America

asked on

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


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?

Avatar of lee555J5
Flag of United States of America image

Convert them to upper case before testing equality, as in


Avatar of NULL_ReferenceException
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?

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