Extract All Remaining Text

[alexandercaleb]

I have users input their preferences by checking certain fruits they would like to have. There are columns for all the fruit options that has a formula attached to create a

✓​

if the individual chose it. If someone has a suggestion I would like for the non fruit items to go into the "Other" Column. I need help extracting only the remaining text excluding the text which already has options in columns (C - G ), if the individual selected it. Is there a way to extract the other text?

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]person name[/TD]
[TD="align: center"]selection / other[/TD]
[TD="align: center"]cherries[/TD]
[TD="align: center"]strawberries[/TD]
[TD="align: center"]grapes[/TD]
[TD="align: center"]oranges[/TD]
[TD="align: center"]mangoes[/TD]
[TD="align: center"]Other[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]Jill[/TD]
[TD="align: center"]cherries, grapes, mangoes, Almonds, cottage cheese[/TD]
[TD="align: center"]=IF(ISNUMBER(SEARCH($C$1, $B2)), "✓", "")[/TD]
[TD="align: center"][/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"][/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"]formula to
extract Almonds, cottage cheese from B2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Kirk[/TD]
[TD="align: center"]strawberries, grapes, trail mix[/TD]
[TD="align: center"][/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]formula to extract trail mix from B3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Nancy[/TD]
[TD="align: center"]cherries, plain coconut milk yogurt,[/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]formula to extract plain coconut milk yogurt from B4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"]grapes, oranges[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Kirk[/TD]
[TD="align: center"]cherries, strawberries, grapes, oranges, mangoes[/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]strawberries, mangoes, coffee, creamer, sugar[/TD]
[TD="align: center"][/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]✓[/TD]
[TD="align: center"]formula to extract coffee, creamer, sugar from B7[/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

I regret that I am not able to devote time to this, partially because of engagements but also that it is somewhat new to me.
That said, I'll give you some solace.

As a small example I built a test case of my thoughts:
[TABLE="width: 512"]
<colgroup><col style="width:48pt" width="64" span="8"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]b[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]ac[/TD]
[TD="class: xl64, colspan: 6"]IF(MID(A7,2,1)=B6,CONCATENATE(LEFT(A7,1), RIGHT(A7,1)),A7)[/TD]
[/TR]
</tbody>[/TABLE]

Here let "abc" be choices made. "b" is the only offer. It has been deleted by the formula which actually resides where the "ac" is appearing. "b" is in cell B6 to give some idea of my orientation.

What you seek should well be possible in the right hands (or you further explore). At least this will bump the topic. Apologies.

 

[alexandercaleb]

[[solved]]