Forum Discussion

Js13ar's avatar
Js13ar
Copper Contributor
Aug 05, 2021
Solved

IF a cell starts with a letter then "value"

Hi, 

 

I have a bunch of customer names that are assigned to an employee depending on what letter the customer name begins. So if the name begins with A-F or S-Z then it is assigned to Employee 1. If the letter begins with H-R then it is assigned to Employee 2. 

ALSO - there are five customer names that are exceptions to this rule. If the customer's name is Red, Blue, Yellow, Green, or Brown then it is automatically assigned to Employee 3. 

 

Is there a way to create a formula to identify which employee each customer's name should be? Please see the formula I have created so far below:

 

=IF(OR(E2="BLUE", "YELLOW", "GREEN", "RED", "BROWN"), Employee 3), IF(OR(LEFT(E2,1)="A-F", "S-Z"), "Employee 1", IF(LEFT(E2,1)="H-R", "Employee 2"))

 

Do I have to list out every letter one by one?

 

 

Thanks!

 

  • Js13ar 

    Try this:

    =IF(OR(E2={"BLUE","YELLOW","GREEN","RED","BROWN"}), "Employee 3", IF(AND(LEFT(E2)>="H", LEFT(E2)<="R"), "Employee 2", "Employee 1"))

    or

    =IF(E2="", "", IF(OR(E2={"BLUE","YELLOW","GREEN","RED","BROWN"}), "Employee 3", IF(AND(LEFT(E2)>="H", LEFT(E2)<="R"), "Employee 2", "Employee 1")))

2 Replies

  • Js13ar 

    Try this:

    =IF(OR(E2={"BLUE","YELLOW","GREEN","RED","BROWN"}), "Employee 3", IF(AND(LEFT(E2)>="H", LEFT(E2)<="R"), "Employee 2", "Employee 1"))

    or

    =IF(E2="", "", IF(OR(E2={"BLUE","YELLOW","GREEN","RED","BROWN"}), "Employee 3", IF(AND(LEFT(E2)>="H", LEFT(E2)<="R"), "Employee 2", "Employee 1")))

    • Js13ar's avatar
      Js13ar
      Copper Contributor
      Worked like a charm. Thanks!

Resources