Generate Description Based on First Three Digits of Another Cell

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!

Workflow

Transformation

Accepted answers

TylerNa

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.

Number and Descriptions.yxmd

All comments

TylerNa

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.

Number and Descriptions.yxmd

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")

Switch Example.png

Eample.yxmd

Pburmester

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

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335