Excel Formula Help: Extract a Value If Cell Contains a Certain Text

[aniysapulliam]

Hello all,

I am trying to create a formula to extract value names where someone 1) Opted In and the Name of the Category.

For example:

E01M01G0NS0N

E0NM1NG0NS0N

0 = Opted Out
1 = Opted In
E = Exchange
M = Membership
G = Getaway
S = Survey
N = No Change

So, in the first example it is saying: Exchange Opted Out then Opted In, Membership Opted Out then Opted In, Getaway Opted Out No Change, Survey Opted Out No Change.

Ultimately, I need a formula that will return the value Exchange, Membership for the first example (signifying the areas where someone Opted In)

For the second example it is saying: Exchange Opted Out No Change, Membership Opted In No Change, Getaway Opted Out No Change, Survey Opted Out No Change

The value would be Membership

Thank you.

 

[shknbk2]

It's long and likely terribly inefficient, but this seems to work. Change A1 to whatever cell is relevant.

=CONCAT(IF(OR(MID(A1,2,2)="01",MID(A1,2,2)="1N"),"Exchange",""),IF(OR(MID(A1,5,2)="01",MID(A1,5,2)="1N"),IF(OR(MID(A1,2,2)="01",MID(A1,2,2)="1N"),", Membership","Membership"),""),IF(OR(MID(A1,8,2)="01",MID(A1,8,2)="1N"),IF(OR(MID(A1,2,2)="01",MID(A1,2,2)="1N",MID(A1,5,2)="01",MID(A1,5,2)="1N"),", Gateway","Gateway"),""),IF(OR(MID(A1,11,2)="01",MID(A1,11,2)="1N"),IF(OR(MID(A1,2,2)="01",MID(A1,2,2)="1N",MID(A1,5,2)="01",MID(A1,5,2)="1N",MID(A1,8,2)="01",MID(A1,8,2)="1N"),", Survey","Survey"),""))