How can search for text that can vary, in a spreadsheet?

Question

I have a form that I have people fill out that links to a Spreadsheet.

One of the questions is multiple choice/selection and stores all of the answers someone gives in one cell delineated by a comma. Example "Answer 1, Answer 2, Answer 3".

I can search for and separate all of the answers pretty easily using

=if(ISNUMBER(search("Phone Call",'Form Responses 1'!G2)) = True,"Yes","No")

The very last option on this question though is an "Other" option where the person can write in their own answer which could be anything. I need to be able to search out or isolate just that other answer and display it in another cell. Is that possible?

Answer 1

Answer:

You can extract all the default options with REGEXEXTRACT and format the rest of the answers.

Formula:

Assuming that your default options are Phone Call, Text Message and Email, you can do the following:

=TRIM(REGEXREPLACE('Form Responses 1'!B2,"Phone Call,|Text Message,|Email,|", ""))

Rundown:

• Use REGEXREPLACE to remove all instances of the strings Phone Call,, Text Message, and Email, from the string contained in 'Form Responses 1'!B2
• Use TRIM to remove the preceding and trailing spaces of the string that's left.

What you need to do:

• Change the options inside the regular expression to match your available multiple choice answers.

Visual Example:

For the following input on the question:

The Sheet will record the data as such:

And the formula will extract the relevant information from the "Other" category:

References:

• REGEXEXTRACT - Docs Editors Help
• TRIM function - Docs Editors Help