I'll focus on just the portion of your formula that you're asking about (the first IF test).
Partial text using a keyword
If the partial text is a unique word or string, you can look for its presence. Excel doesn't have a "CONTAINS()" function, but you can do the equivalent with some nesting.
=IF(ISERROR(SEARCH("mix",E6)),"","By Mix Design")
SEARCH looks for the target text (not case sensitive), in E6 (I used lower case for "mix" to illustrate this). If it doesn't find it, an error is returned. Testing for that with ISERROR gives the reverse of the logic for "contains", so I reversed the True and False results.
If you want a case-sensitive test, use FIND instead of SEARCH.
Partial text based on position
Excellent point by Engineer Toast in the comment that I'll add here for completeness. If the first word (or some number of characters), is unique, you can test those characters. To do a case-sensitive test, test the actual characters:
=IF(LEFT(E6,3)="Mix","By Mix Design","")
If case might not be consistent in your data, test against characters that are forced to a specific case:
=IF(UPPER(LEFT(E6,3))="MIX","By Mix Design","")
And just to cover the bases, if your situation is that your partial text always occurs in a specific position in the string, you can do a more precise test by looking for it there rather than anywhere in the string. Suppose your keyword was "Design" and it always starts in the fifth character position in the string, you could use:
=IF(MID(E6,5,6)="Design","By Mix Design","")
Note that if there is inconsistency in the data, like a missing character or extra space so that the keyword doesn't start in the fifth position, the test will fail. The SEARCH/FIND solution is more forgiving in that the position of the keyword doesn't matter.