What's the syntax when using a formula instead of a value as the logical expression?

Question

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?

Answer 1

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.