Cell begins with

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.