Finding a string within a longer string and expressing a different string

[Tonks]

Hi All,

I'm trying to create a formula that will allow me to search 3 different strings within a field that contains a long piece of text and then give me 3 separate and different strings depending on what is found. As per the table below I will be searching for either Class1, Class3, or Class 4. If Class1 is found then I need the value CLS1, if Class3 is found I need the value CLS3 and finally (arguably the tricky aspect) if Class4 is found I need the row (and all its columns) to be copied and one allocated to CLS1 and the other CLS3.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Role[/TD]
[TD]Long Text[/TD]
[TD]Extraction[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Class1, Class2, [/TD]
[TD]CLS1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Class3[/TD]
[TD]CLS3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Class4[/TD]
[TD]CLS1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Class4[/TD]
[TD]CLS3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope this makes sense, if there is anyone who can offer any input it would be greatly appreciated!

Thanks,

Tonks

 

[Jonmo1]

Welcome to the board.

Make a table listing the strings you're looking for, and the string you want returned if it's found
Say E2:F4
E2:E4 = Class1, Class3 and Class4
F2:F4 = CLS1, CLS3 and CLS4

Then if A2 holds the long string being searched, Use

=IFERROR(LOOKUP(2^15,SEARCH($E$2:$E$4,A2),$F$2:$F$4),"")

 

[Tonks]

Thanks a lot Jonmo that's a great help that will work when the function finds Class1 and Class3. Do you or anyone else for that matter have a suggestion for Class4? Which is basically the culmination of Class1 and Class3 and when found the single row needs to duplicated and then one assigned the value Class1 and the other Class4. Please shout if this isn't clear and I can explain in more detail.