Index Loop?

[LehiVitor]

Hello, 

I have several different materials to I need to remove the last 2 characters to convert from Loaner Material to Material. I was able to use the Left and Index functions in an IF statement which worked for the first 4 loops, but I am not able to get it to work on the last loop. 

Is there a limit to how many IF loops I can create? 

Or does anyone know of a better way to set up this function? 

IF(isnull(Left(Material, Index(Material,'=A',-1)-1)),

if(isnull(Left(Material, Index(Material,'-A',-1)-1)),

if(ISNULL(Left(Material, Index(Material,'-LNR',-1)-1)),

if(isnull(Left(Material, Index(Material,'-S',-1)-1)),

if(isNull(Left(Material, Index(Material,'=S',-1)-1)),

Left(Material, Index(Material,'=S',-1)-1),1),

Left(Material, Index(Material,'-S',-1)-1)),

Left(Material, Index(Material,'-LNR',-1)-1)),

Left(Material, Index(Material,'-A',-1)-1)),

Left(Material, Index(Material,'=A',-1)-1)) as Material,

[Kushal_Chawda · Accepted solution]

@LehiVitor  you can try with Pick match approach

Pick(WildMatch(LoanerMaterial,'*-A*','*=A*','*-S*','*=S*','*-LNR*')+1,
     LoanerMaterial,
     Left(LoanerMaterial,Index(LoanerMaterial,'-A')-1),
      Left(LoanerMaterial,Index(LoanerMaterial,'=A')-1),
     Left(LoanerMaterial,Index(LoanerMaterial,'-S')-1),
      Left(LoanerMaterial,Index(LoanerMaterial,'=S')-1),
     Left(LoanerMaterial,Index(LoanerMaterial,'-LNR')-1)) as Material