my question is when ever i try any combination i should get one of the result
row 1 is first letter of cells
row 2 is if Cell A is blank then two letter of Cell B then one letter of Cell C
row 3 is if Cell A and Cell C is blank then three letter of Cell B
row 4 is if Cell C is blank then one letter of Cell A then two letter of Cell B
combine all four row in one row any combination i should get one of the result

D1 formula =Concatenate(Left(A1,1),Left(B1,1),Left(C1,1)) 
D2 formula =Concatenate(Left(B2,2),Left(C2,1)) 
D3 formula =Concatenate(Left(B3,3)) 
D4 formula =Concatenate(Left(A4,1),Left(B4,2))

D1 formula =(LEFT(A1,1)&LEFT(B1,1)&LEFT(C1,1))
D2 formula =(LEFT(B2,2)&LEFT(C2,1))
D3 formula =(LEFT(B3,3)) 
D4 formula =(LEFT(A4,1)&LEFT(B4,2))

combine all in one when entered any of the order display specific answer

CodePudding user response：

Just combine all your conditions into a single formula with SEVERAL if:

=IF(COUNTA(A1:C1)=3;LEFT(A1;1)&LEFT(B1;1)&LEFT(C1;1);IF(AND(A1="";COUNTA(B1:C1)=2);LEFT(B1;2)&LEFT(C1;1);IF(AND(A1="";C1="";B1<>"");LEFT(B1;3);IF(AND(COUNTA(A1:B1)=2;C1="");LEFT(A1;1)&LEFT(B1;2);"No match"))))

Indented:

=IF(COUNTA(A1:C1)=3;
    LEFT(A1;1)&LEFT(B1;1)&LEFT(C1;1);
    IF(AND(A1="";COUNTA(B1:C1)=2);
        LEFT(B1;2)&LEFT(C1;1);
        IF(AND(A1="";C1="";B1<>"");
            LEFT(B1;3);
            IF(AND(COUNTA(A1:B1)=2;C1="");
                LEFT(A1;1)&LEFT(B1;2);
                "No match")
            )
        )
    )

CodePudding user response：

=IF(COUNTA(A1:C1)=3, LEFT(A1,1)&LEFT(B1,1)&LEFT(C1,1), IF(AND(A1="",COUNTA(B1:C1)=2), LEFT(B1,2)&LEFT(C1,1), IF(AND(A1="",C1="",B1<>""), LEFT(B1,3), IF(AND(COUNTA(A1,B1)=2,C1=""), LEFT(A1,1)&LEFT(B1,2), "No match") ) ) )