Formula Friday – Easily Identify Cells That Begins With A Specified Letter in Excel


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.

identify Excel cells that begin with a specific letter

We begin by selecting all of the data set

  • Home Tab
  • Styles Group
  • New Rule
find cells that begin with specified letters in Excel
  • 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

find cells that begin with specified letters in Excel with conditional formatting
  • Select a type of conditional formatting of cells to suit your requirements

In this example I want to look for the letter K.

find cells that begin with specified letters in Excel2

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.

How To Excel At Excel – Macro Mondays Blog Posts.


How To Excel At Excel – Formula Friday Blog Posts.

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts