0

This is what I'm using right now in the formula:

=IF(E6="Mix Design Gr S","By Mix Design","")&IF(E6="R-Value","By Soils","")&IF(E6="PS Package","By Soils","")

Is there a way to only look for partial text in cell E6 such as "Mix" instead of "Mix Design Gr S"?

1 Answer 1

0

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.

3
  • Key Point: If you only want to test if "Mix" is as the beginning of the string, use UPPER(LEFT(E6,3))="MIX" Jan 28, 2016 at 16:12
  • @EngineerToast: Don't know how I missed that. Thanks.
    – fixer1234
    Jan 28, 2016 at 17:19
  • This one did the trick: =IF(LEFT(E6,3)="Mix","By Mix Design","") Thank you all very much for your help.
    – Dan K
    Jan 29, 2016 at 13:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .