2

I've just found about the very handy use of boolean logic to shorten/replace multiple Nested If Statement from this question (4th answer):

Is there a way to shorten multiple if statements?

With this given example as substitute for multiple nested IF statements:

Multiple Nested IF Statements version:

= IF(E3 = "red", 100, IF(E3 = "blue", 200, IF(E3 = "green", 300, IF(E3 = "orange", 400, 500))))

Vs

Boolean Logic version:

= (E3 = "red") * 100 + (E3 = "blue") * 200 + (E3 = "green") * 300 + (E3 = "orange") * 400 + (E3 = "purple") * 500

That works well when returning a number is intended.

If true, the following returns 100 or 200.

=(REGEXMATCH(B19,F2)) * 100 + (REGEXMATCH(B19,F40)) * 200

But I get MULTIPLY or ADD errors when trying to return strings.

For example, with the 2 strings "Time" in cell F2 and "Test" in cell F40:

=(REGEXMATCH(B19,F2)) * F2 + (REGEXMATCH(B19,F40)) * F40

returns this error:

Function MULTIPLY parameter 2 expects number values. But 'Test' is a text and cannot be coerced to a number.

=(REGEXMATCH(B19,F2)) & F2 + (REGEXMATCH(B19,F40)) & F40

returns this error:

Function ADD parameter 1 expects number values. But 'Test' is a text and cannot be coerced to a number.

=(REGEXMATCH(B19,F2)) & F2 & (REGEXMATCH(B19,F40)) & F40

returns:

FALSETimeTRUETest

My question:

Is there a way to use Boolean logic with strings? If possible as concise as the way with numbers?

What operator is available for strings if there are some?

Many thanks for your help and insights!

Improve this question
1

1 Answer 1

Reset to default
3

The reason that the boolean solution is working with (REGEXMATCH(B19,F2)) * F2 when F2 is a number is that the formula translates TRUE|FALSE * F2, and sheets automatically converts TRUE|FALSE to 1|0 respectively if it detects that what you are multiplying/adding is a number since it is supported.

When F2 is a string, it doesn't support multiplying/adding a string and a number thus causes your issue.

What you can use is a simpler version of IF which doesn't need nesting, looks and functions like what you are doing. This is IFS.

Your formula:

=((REGEXMATCH(B19,F2)) * F2) + ((REGEXMATCH(B19,F40)) * F40)

Turns into:

=IFS(REGEXMATCH(B19,F2), F2, REGEXMATCH(B19,F40), F40)

The above solution can also be used on numbers as well.

Reference:

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks a lot Octavia for the useful explanation of the TRUE|FALSE conversion to 1|0 ! I didn't know about that Sheets background way to make it work with numbers. Thanks also for the IFS workaround suggestion. I was wondering if there was a way to use the boolean method to avoid the many more parenthesis (it feels so much simpler for example =REGEXMATCH(B19,F2) * 100 + REGEXMATCH(B19,F40) * 200 to work!) and possibly also potential sequential order processing constraints with the IFS solution. I'll see if I can make it work for my use. Thanks again!
I've worked out an alternative by using a assigning a number value to each F2 strings and then using a VLOOKUP to return the string when the number is returned by the REGEXMATCH. That works for my current use.
I have to note that any of your current formula can easily be converted to the proposed formula above. You just have to convert all + and * to , and then place it inside an IFS statement. Also, combining boolean method and strings may not be the best implementation for this specific issue. I might get it to work but it might introduce more characters like parenthesis, commas, etc. Sorry as I may not be able to provide the specific thing you ask. Other users might see this question later and chime in here with a better answer. Let's wait and see.
It would be great to have a kind of Boolean Logic background operation specific to strings (though I'm not sure if that would be possible). Instead of the multiplication operation, why not use some kind of string match function to return the string? For example, converting the TRUE to the 'string value' instead of 1. Then instead of multiplying by 1, simply checking that the 'string value' match itself and if it does returning it. That would make an added 'string specific' boolean match operator (something as concise as the +-/* ones would be great). Thanks again!

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.