Hello, Excellers. It is Formula Friday time again!. Do you need to identify with conditional formatting cells that begin with certain letters?. Today I will show you how Excel can easily identify cells that begin with a specified letter. To highlight these cells, we can use Conditional Formatting to help identify the cells that meet our criteria. All we need to do is use two Excel functions. We can use the LEFT and the LEN Function.
Let’s take a recap of the syntax of both of these functions before we dive into the Excel solution.
The LEFT Function.
This will extract a substring from a string beginning from the left-hand side.
=LEFT(text, number of characters)
where
text . This is the string that you want to extract from.
number of characters. This is the number of characters from the left-hand side that you want Excel to extract. This is an optional argument and if left blank Excel will extract only 1 character by default
The LEN Function.
This function returns the length of a specified string.
=Len(text)
where
text. This is the string that you want to return the length of. Simple right?.
The Conditional Formatting Solution.
So, let’s get started with a sample data set to work with. We will use cell G2 to instruct Excel as to which letter to look for in our data set of cells. Conditional formatting can then be applied to those cells.
We begin by selecting all of the data set
- Home Tab
- Styles Group
- New Rule
- We want to use Use A Formula To Determine Which Cells To Format
- In the Edit Rule Description type the following formula
=LEFT(B2,LEN($G$1))=$G$1
- Select a type of conditional formatting of cells to suit your requirements
In this example I want to look for the letter K.
All of the cells that begin with K are identified. Give this a go, or do you have an alternative method to identify specific cells.