Generate Description Based on First Three Digits of Another Cell

[Pburmester]

Hello everyone,

I am having some difficulty with a very simple formula. I want to generate a description in Column B based on the first three digits of the number in Column A. In Excel, it would be similar to an IF statement that contains the LEFT statement:

=If(left(CELL,3)="420","Description1",IF(left(CELL,3)="496","Description2",IF(left(CELL,3)="492","Description3")))

I've tried a Find and Replace and a Formula but have not been able to figure out a solution to this very simple task.

First three digits:

420 = Description1

496 = Description2

492 = Description3

Current Data Input:

Number	Description
42042
42061
49643
49647
49243
49261

Desired Output:

Number	Description
42042	Description1
42061	Description1
49643	Description2
49647	Description2
49243	Description3
49261	Description3

Any help would be much appreciated!

[TylerNa · Accepted solution]

It sounds like you're on the right track @Pburmester. The attached workflow will give you one way to get at this. I just created a left formula and then a if then statement.

[darryl5280]

I would use the Switch Operator as I think it's more elegant.

/* 420 = Description1
496 = Description2
492 = Description3
*/
Switch(Left(ToString([Number]),3),"Unknown",
"420","Description1",
"496","Description2",
"492","Description3")

[Pburmester]

Ah I knew it was an easy solution. Thank you for the help!