Conditional formatting based off first letter

Copper Contributor
Hello,

I am trying to conditionally format a list of names based off the first letter in the cell. I want to have the cells containing A-L set to one format and M-Z as a different format. Is there one formula to format based off a range of text values of the first letter?
3 Replies

@Evannecarter 

You'll need to create two rules, unless you want one of the two formats to be the default formatting of the cells.

Select the cells you want to format.

 

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'less than' from the second drop-down.

In the box next to it, enter the formula ="M"

Click Format...

Specify the desired formatting, for example a specific font color and/or fill color.

Click OK, then click OK again.

 

Repeat the above steps, but with 'greater than or equal to' instead of 'less than', and with different formatting.

 

S0766.png

Hi, I've just tried this but it only works if one letter is entered in the cell. Have I done something wrong? Thanks :)

@Kiwichick 

It should work for entries with more than one letter too:

S1186.png

See the attached sample workbook.