if(isnumber(search(not this text?)

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

Excel Formula.xlsx

Here's the example with before & after tabs. Thanks

Problem is the data collection has already been done so this is what I've got. Looks like I might have to go through by hand. Thanks for looking anyway!