Hi,

I have one row with different survey answers, i.e., I asked people to choose where they might like to give birth: home, hospital, etc. I needed to turn these into 1 if yes and 0 if no so created other rows for each option with (example) if(isnumber(search("home",A2)),1,0) and this works but the problem I have is that I also gave people an 'other' option and this also shows up in the same cell as their other answers so you might get: "home, hospital, small local hospital". I set up excel to display the 'other' data if my other rows are all 0, so they haven't also picked one of my options (88 is missing), using this: IF(AND(DN2=0,DM2=0,DL2=0,DK2=0,DJ2=0,DI2>0),DI2,IF(OR(DN2=1,DM2=1,DL2=1,DK2=1,DJ2=1),"",88)) so if all of my other rows for home, hospital etc. were 0 it would display what they have put. Problem is, I need their 'other' option here even if they also said one of my options and it won't appear because it's already counted their option.

Is there a way to tell excel to display their 'other' if it also says one of my options? Something like if(isnumber(search(not?"Home","Hospital" etc...))"their other option","")

Hope this makes sense!

Thanks for any help