Explanation
In this example, the goal is to test values in column B to see if they begin with a specific text string, which is “xyz” in the worksheet shown. This problem can be solved with the LEFT function, as explained below.
LEFT function
The LEFT function extracts a given number of characters from the left side of a text string. For example, the formula below returns the first three letters of “apple”, which is “app”:
=LEFT("apple",3) // returns "app"
This means we can use the LEFT function to test if cell B5 begins with “xyz” like this:
=LEFT(B5,3)="xyz"
The LEFT function extracts the first 3 characters in cell B5 and the result is compared to the string “xyz” forcing a TRUE or FALSE result. The formula is solved like this:
=LEFT(B5,3)="xyz"
=LEFT("ABC-1224-HNN",3)="xyz"
="ABC"="xyz"
=FALSE
For cell B5 the result is FALSE, since “ABC-1224-HNN” does not begin with “xyz”. In cell B6, however, the result is TRUE, since “XYZ-6543-JWB” does begin with “xyz”.
=LEFT(B6,3)="xyz"
=LEFT("XYZ-6543-JWB",3)="xyz"
="XYZ"="xyz"
=TRUE
Note that Excel is not case-sensitive by default, so “XYZ”=“xyz” will return TRUE in a formula. Also note the num_chars argument is set to 3 above, but must be modified according to the situation. For example, to test for a value that begins with “apple”, num_chars should be set to 5:
=LEFT(B5,5)="apple"
Case-sensitive option
Excel is not case-sensitive by default, but you can easily adapt the formula to use the EXACT function to make the formula case-sensitive like this:
=EXACT(LEFT(B5,3),"xyz")
EXACT takes two arguments, text1 and text2 . EXACT will only return TRUE when text1 and text2 are exactly the same, taking into account case. For example:
=EXACT("abc","ABC") // returns FALSE
=EXACT("abc","Abc") // returns FALSE
=EXACT("abc","abc") // returns TRUE
Turning back to cell B6 in the worksheet shown, the two formulas below return different results:
=EXACT(LEFT(B6,3),"xyz") // returns FALSE
=EXACT(LEFT(B6,3),"XYZ") // returns TRUE
The first formula returns FALSE because the EXACT function is case-sensitive, so “XYZ” does not equal “xyz”. The second formula returns TRUE because “XYZ” does equal “XYZ” taking into account case. Note we don’t need the equal to operator (=) in this formula since EXACT performs a comparison automatically.
If cell begins with
To adapt the formulas above to “If cell begins with”, simply drop the formulas into the IF function as the logical test. For example, to return “Yes” when a cell contains “xyz” and “No” when not, you can use a formula like this
=IF(LEFT(B5,3)="xyz", "Yes", "No")
The case-sensitive version of the formula works the same way:
=IF(EXACT(LEFT(B5,3),"XYZ"), "Yes", "No")
Other functions
It is worth noting that Excel contains two other functions, the SEARCH function and the FIND function that are meant to look for a substring in a text value. They could be used to solve this problem, but they are more work to configure in this case, and the resulting formulas are more complicated, so I don’t see any advantage to using them.
Alternately, you could use the COUNTIF function with a wildcard to solve this problem like this:
=COUNTIF(B5,"xyz*")<>0
This works fine, but keep in mind that COUNTIF is in a group of eight *IFS functions that won’t accept an array for the range argument. This means you can’t use COUNTIF to test values in an array returned by another operation. I don’t like this limitation, so I avoid the *IFS functions when there is a good alternative.
Explanation
In this example, the goal is to build a formula that will return TRUE if a given cell contains all values that appear in a given range. We could build a formula that uses nested IF statements to check for each value, but that won’t scale well if we have a lot of values to test because each new value will require another nested IF. The article below explains a more scalable approach based on the SEARCH function. For convenience, the values we are testing for are in the named range “things” which is F5:F7. The formula in cell D5 looks like this:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
At a high level, this formula counts the matches found in cell B5 for the three values in things (F5:F7). Then it compares the count of matches found with the count of values in things . If the counts match, the formula returns TRUE. Otherwise, the formula returns FALSE. Working from the inside out, the core of the formula is this snippet based on the SEARCH function and the ISNUMBER function :
ISNUMBER(SEARCH(things,B5)
This code is based on a fairly common pattern in Excel formulas ( explained in detail here ) that are designed to test a cell for a specific text. The more generic version of the formula looks like this:
ISNUMBER(SEARCH(substring,text)
In a nutshell, the SEARCH function searches text for a substring . If the substring is found, SEARCH returns the location of the match as a number. If the substring is not found, SEARCH returns a #VALUE error. The ISNUMBER function is used to convert the result from SEARCH into TRUE or FALSE.
The twist in this formula is that we are not searching for a single substring. Instead, we are searching for 3 different substrings in the named range things (F5:F7). Because we are giving SEARCH three different substrings to look for, SEARCH will return three separate results in an array . In cell B5, the results from SEARCH look like this:
{7;1;16}
These numbers represent the location of “red”, “blue”, and “green” in the text from cell B5. The text “red” begins at character 7, “blue” begins at character 1, and “green” begins at character 16. These locations are delivered to the ISNUMBER function, which converts the results to TRUE or FALSE values. Because all three results are numbers, ISNUMBER returns an array that contains three TRUE values:
=ISNUMBER(SEARCH(things,B5))
=ISNUMBER({7;1;16))
={TRUE;TRUE;TRUE}
Next, we convert the TRUE / FALSE values to 1s and 0s with a double negative (–) operation:
--{TRUE;TRUE;TRUE}
The result is an array like this:
{1;1;1}
We take this step because we want to work with TRUE and FALSE like the numbers 1 and 0. Next, we process this array with SUMPRODUCT, which will give us a total count of matches:
=SUMPRODUCT({1;1;1}) // returns 3
The final step in the formula is to compare this result to the count of values in the named range things (F5:F7). To get a count of the values in things, we use the COUNTA function , which is designed to count both numbers and text:
=COUNTA(things)
=COUNTA({"red";"blue";"green"})
=3
If the counts are equal, the formula will return TRUE:
=SUMPRODUCT({1;1;1})=COUNTA(things)
=3=3
=TRUE
With a hard-coded list
There’s no requirement to use a range for your list of things. If you’re only looking for a small number of things, you can use a list in array format, called an array constant . For example, if you’re just looking for the colors red, blue, and green, you can use {“red”,“blue”,“green”} and hardcode a count of values like this:
=SUMPRODUCT(--ISNUMBER(SEARCH({"yellow","green","dog"},B5)))=3
Simplifying the formula
There is a fair bit of complexity in computing a count of the substrings found, which involves ISNUMBER, a double negative (–) operation, and SUMPRODUCT. You might wonder why you can’t just do this:
=COUNT(SEARCH(things,B5))=COUNTA(things)
