May 13, 2017

Convert separated by commas data into columns without 'text to column'

if you want user to select multiple items of choice on google form like below,
The output in google sheet will be like below

and you would like the data to come in separate columns for ease of data analysis like below


The solution is this simple formula

search(C$1,$B2) : Search content in C2 cell in B3 and tell at which place it came
So essentially, if result is greater than 0 > it indicates that the searched term was available.

if you just add the "if logic", it will give you result in Yes - 1, No - 0
if(search(C$1,$B3)>0,1,0)

and add iferror, you wouldn't get #N/A error.