G
Guest
Well it's complex for me
I have created the following formula which in the main works well, but I am
wanting to enhance it further with other criteria below and struggling.
=IF(M22="","",(IF(LEFT(M22,2)="XD",VLOOKUP(M22,'\\WYP_GEN_1\EA_DIV$\Duties\[Callsigns.xls]CollarNo'!$A$1:$D$100,4,FALSE),(IF(LEFT(M22,3)="TW1",CONCATENATE("TW1",RIGHT(M22,2)),(IF(LEFT(M22,3)="STW",CONCATENATE("STW",RIGHT(M22,3)),(IF(LEFT(M22,3)="CS1",CONCATENATE("CS01",RIGHT(M22,2)),(IF(LEN(M22)=2,CONCATENATE("00",M22),IF(LEN(M22)=3,CONCATENATE("0",M22),(TEXT(M22,"0"))))))))))))))
I want to be able to include the following as well:-
IF(LEFT(M22,3)="CS2",CONCATENATE("CS02",RIGHT(M22,2))
&
IF(LEFT(M22,3)="CS3",CONCATENATE("CS03",RIGHT(M22,2))
&
IF(LEFT(M22,1)="S",RIGHT(M22,4)
Any assistance would be greatly appreciated, thanks
I have created the following formula which in the main works well, but I am
wanting to enhance it further with other criteria below and struggling.
=IF(M22="","",(IF(LEFT(M22,2)="XD",VLOOKUP(M22,'\\WYP_GEN_1\EA_DIV$\Duties\[Callsigns.xls]CollarNo'!$A$1:$D$100,4,FALSE),(IF(LEFT(M22,3)="TW1",CONCATENATE("TW1",RIGHT(M22,2)),(IF(LEFT(M22,3)="STW",CONCATENATE("STW",RIGHT(M22,3)),(IF(LEFT(M22,3)="CS1",CONCATENATE("CS01",RIGHT(M22,2)),(IF(LEN(M22)=2,CONCATENATE("00",M22),IF(LEN(M22)=3,CONCATENATE("0",M22),(TEXT(M22,"0"))))))))))))))
I want to be able to include the following as well:-
IF(LEFT(M22,3)="CS2",CONCATENATE("CS02",RIGHT(M22,2))
&
IF(LEFT(M22,3)="CS3",CONCATENATE("CS03",RIGHT(M22,2))
&
IF(LEFT(M22,1)="S",RIGHT(M22,4)
Any assistance would be greatly appreciated, thanks