0

My SWITCH function returns false when the logical expression is a formula in a cell.

Here's what works as expected:

=SWITCH(B3,12,"DEC","NONE")

output is DEC where B3 contains the value 12

However, if B3 contains the formula: =left(A3,2)

and A3 contains the value 12345 then B3 is correctly displaying the value 12 but the SWITCH function returns NONE.

I've tried this:

=SWITCH(LEFT(A3,2),12,"DEC","NONE")

but that also returns NONE.

So, how do I get the function to recognize the value of the cell with the formula?

1 Answer 1

0

You must understand that each piece of data has a data type: string, number or Boolean. Even if those look or seem the same when you see them, they are not.

So the number 12 is not the same as the string "12"; and computers know the difference. (If you're interested, Boolean values are TRUE and FALSE, which are not the same as the strings "TRUE" and "FALSE.")

Functions like LEFT are string functions. Even if the source data is a number, LEFT (etc.) force the output to be a string.

You have a couple of choices with your formula then:

1.) =SWITCH(LEFT(A3,2),"12","DEC","NONE")

2.) =SWITCH(LEFT(A3,2)*1,12,"DEC","NONE")

Here, multiplying the string by 1 with *1 forces it back to being a number, just as applying LEFT forced the source data to be a string.

However, if A3 might ever result in some string that cannot be coerced to a number, then option #2 here will result in an error; so you'd want to use option #1 in that case.

2
  • Makes perfect sense, Erik. Thank you! Feb 18, 2022 at 0:50
  • If you would, please take a moment to mark the solution post as "Best Answer." This let's the volunteer community here know at a glance from the main listings page which posts have already been addressed successfully; and it allows future site visitors to more easily find the answer that match their own needs and search terms. Thanks.
    – Erik Tyler
    Feb 18, 2022 at 1:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.