Includes or Contains formula for String Values

[AL3XE167]

Hi,

I am trying to cleanse some messy survey data. The survey has a number of questions that are multiple choice. The respondent is able to select a number of choices per answer. Consequently, when viewed in Alteryx the cell could look like this;

Respondent_010	answer a, answer b, answer d
Respondent_011	answer a
Respondent_012	answer a, answer d

Cleansing this is relatively easy, Ive been using text to columns on commas, transposing and then filtering out the nulls.

However, one of the multiple choice options is "Other" this then allows the respondent to write free text;

Respondent_001	answer a, answer d
Respondent_002	I am not impressed with this questionnaire. I think it is a waste of my time, you have wasted resource.

I want to treat these answers differently. Im unable to filter on "Other" as that isn't contained in the data, only their free text response.

I want to filter all the "normal" answers e.g. "answer A, answer B" and then treat the free text differently so ultimately want to split them out into two streams of data. The difficulty is the combination of answers available, so I cant simply write an if statement with if field = "Answer A, Answer B"  then......

Having looked through the community I tried a if REGEX_Match formula;

if REGEX_Match ([Field_A], "answer A|answer B|answer C| answer D" then "Text to Columns" Else "Free Text" endif

However this didnt work and I now don't know how to proceed.

Any ideas?

Thanks in advance.

Alex

[markcurry]

Hi @AL3XE167 

Try this...

if REGEX_Match ([Field_A], "answer\s\D.*") then
"Text to Columns"
Else
"Free Text"
endif

Hope that helps,

[mbarone]

I would try to future proof it and look for:

a string that has "answer" in it somewhere, followed by a space, then either a letter and a comma, or a letter and a space, or a letter and the end of the line:

REGEX_Match([Response],'.*answer\s[a-zA-Z]\s .*') OR
REGEX_Match([Response],'.*answer\s[a-zA-Z],.*') OR
REGEX_Match([Response],'.*answer\s[a-zA-Z]$.*')

[AL3XE167]

Sorry, I should have been more specific than Answer A, Answer B etc etc

The Answers vary entirely from things such as;

Trouble with Childcare, Issues with my line manager, worried about public transport

[atcodedog05]

Hi @AL3XE167 

You so you need to say Answer A is not Answer A

but Answer A might be Trouble with Childcare

Is that right?

If yes you have a list of answers Answer A-Z

[atcodedog05 · Accepted solution]

Hi @AL3XE167 

Here is a workflow that might help.

Output:

In options

Out of options

Basically i am doing a lookup with actual options to find others.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[mbarone]

Any detail you can give or a few real examples might help.

[atcodedog05]

Happy to help 🙂 @AL3XE167 

Cheers and Happy Analyzing 😀

[AL3XE167]

Thanks for your help!