SOLVED

Contributor

Formulas and Functions

Dear Everyone,

 A B C Particulars Criteria Result 1 101010100000 If 1st digit of Cell A1 is 1 and 3rd digit of Cell A1 is 1, Cell C1 will show CA CA 2 102020100000 If 1st digit of Cell A2 is 1 and 3rd digit of Cell A2 is 2, Cell C2 will show NCA NCA 3 103010000000 If 1st digit of Cell A3 is 1 and 3rd digit of Cell A3 is 3, Cell C3 will show IA IA 4 104010300000 If 1st digit of Cell A4 is 1 and 3rd digit of Cell A4 is 4, Cell C4 will show INV INV 5 201010100000 If 1st digit of Cell A5 is 2 and 3rd digit of Cell A5 is 1, Cell C5 will show SE SE 6 202030300000 If 1st digit of Cell A6 is 2 and 3rd digit of Cell A6 is 2, Cell C6 will show NCL NCL 7 203030300000 If 1st digit of Cell A7 is 2 and 3rd digit of Cell A7 is 3, Cell C7 will show CL CL 8 301010100000 If 1st digit of Cell A8 is 3 and 3rd digit of Cell A8 is 1, Cell C8 will show OE OE 9 302020000000 If 1st digit of Cell A9 is 3 and 3rd digit of Cell A9 is 2, Cell C9 will show Other Other 10 401010200000 If 1st digit of Cell A10 is 4 and 3rd digit of Cell A10 is 1, Cell C10 will show Exp Exp 11 402010200003 If 1st digit of Cell A11 is 4 and 3rd digit of Cell A11 is 2, Cell C11 will show FC FC

4 Replies

Re: Formulas and Functions

Here is an approach for A1 because you could switch to the other lines.
Maybe it helps

=IF(MID(A1,1,1)="1",IF(MID(A1,3,1)="1","CA",""))

...if it's not what you're looking for, please just ignore it

best response confirmed by Oliullah_Siddique (Contributor)
Solution

Re: Formulas and Functions

@Oliullah_Siddique You can do that in several ways. I've included just two of them in the attached workbook. It all depend a bit on how much data yo have, how often you need to change codes etc.

In general, I prefer the solution with a lookup table. The formula get easier to write and read and it easier to maintain.

Re: Formulas and Functions

Thanks @Riny_van_Eekelen

Exactly what I was looking for.

Re: Formulas and Functions

Thank you very much @NikolinoDE