multiple selections of long text

YJ Li

Hi,

I want to make a multiple selections in my IF function, I am aware of CHAR(10) function. However, it does not work in my case, I am suspecting it is because of the selection I want is a string of text but not sure. Please see example below.

=IF([Select all items needed]@row = "Form No. 1234/1234A" + CHAR(10) + "Data collection forms such as 225A, 1234, 225C", 1, 0)

Any suggestion is appreciated.

Paul Newcome

Are you getting an error? What exactly is happening that you are not expecting to happen?

YJ Li

So my question has five selection and these two (Form No. 1234/1234A & Data collection forms such as 225A, 1234, 225C) are the correct answer, there is no error message, it's just not returning the correct response (which should be 1) when I select those two options.

Paul Newcome

Double check that the spelling in the formula and the spelling in the dropdown selections are an exact match by opening up the column properties and comparing it to the formula in the sheet. It is possible that there could be an extra space somewhere in the column properties, but extra spaces are not displayed (example: this comment vs the screenshot from before I posted it).

One space between words

Five Spaces between words

YJ Li

Is there other way to double check the drop down selection? I checked the spelling/spacing in the column property, tried to copy the text directly from the column property, and re-type the value in the drop-down list. None of them work...

YJ Li

It works after I switched the place of those two selections, not sure why though.

=IF([Select all items needed]@row ="Data collection forms such as 225A, 1234, 225C"+ CHAR(10) + "Form No. 1234/1234A" , 1, 0)

Paul Newcome

I completely forgot... The data is stored on the back-end in alphabetical order. That's why it works when you switch. D comes before F.

YJ Li

@Paul Newcome Thanks for the explanation! I am a new user, it's good to know the data storage structure, it makes sense now.

Paul Newcome

Happy to help. 👍️

My apologies for not thinking of that sooner. I'm one of THOSE people who rearranges the dropdown options to be in alphabetical order, so I forget about that little quirk. Haha.