Forum Discussion
Formulas and Functions
Dear Everyone,
Would you please help me with the following formula,
| 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 |
Thanks in advance.
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.
4 Replies
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.
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 🙂
- Thank you very much NikolinoDE
