SOLVED

Formulas and Functions

Contributor

Dear Everyone,

 

@Riny_van_Eekelen 

 

Would you please help me with the following formula,

 

Screenshot 2022-06-21 130555.png

 

 ABC
 ParticularsCriteriaResult
1101010100000If 1st digit of Cell A1 is 1 and 3rd digit of Cell A1 is 1, Cell C1 will show CACA
2102020100000If 1st digit of Cell A2 is 1 and 3rd digit of Cell A2 is 2, Cell C2 will show NCANCA
3103010000000If 1st digit of Cell A3 is 1 and 3rd digit of Cell A3 is 3, Cell C3 will show IAIA
4104010300000If 1st digit of Cell A4 is 1 and 3rd digit of Cell A4 is 4, Cell C4 will show INVINV
5201010100000If 1st digit of Cell A5 is 2 and 3rd digit of Cell A5 is 1, Cell C5 will show SESE
6202030300000If 1st digit of Cell A6 is 2 and 3rd digit of Cell A6 is 2, Cell C6 will show NCLNCL
7203030300000If 1st digit of Cell A7 is 2 and 3rd digit of Cell A7 is 3, Cell C7 will show CLCL
8301010100000If 1st digit of Cell A8 is 3 and 3rd digit of Cell A8 is 1, Cell C8 will show OEOE
9302020000000If 1st digit of Cell A9 is 3 and 3rd digit of Cell A9 is 2, Cell C9 will show OtherOther
10401010200000If 1st digit of Cell A10 is 4 and 3rd digit of Cell A10 is 1, Cell C10 will show ExpExp
11402010200003If 1st digit of Cell A11 is 4 and 3rd digit of Cell A11 is 2, Cell C11 will show FCFC

 

Thanks in advance.

4 Replies

@Oliullah_Siddique 

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

@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.

Thanks @Riny_van_Eekelen

Exactly what I was looking for.
Thank you very much @NikolinoDE