How to Apply formula and concatenate

[Kavya432]

Hi Team,

I am trying to apply the formula. In the attached data set If Exclusive Producer Number (length of string) is equals to 9 The I have to Concatenate "00" after first two letter and then have to place rest 5 letters beside those "00". If Exclusive Producer Number (length of string) is not equals to 9 then just need to have "NA"(all this needs to be done in new column)

Could you please help me to guide through the process of achieving this. 

Thanks

Kavya

[Thableaus · Accepted solution]

Hi @Kavya432 

I'm not so sure how your data looks like, but would something like this work?

IF Length([Exclusive Producer Number]) = 9 THEN

Left([Exclusive Producer Number], 2) + '00' + Right([Exclusive Producer Number],7)

ELSE

"NA" ENDIF

[jamielaird · Accepted solution]

IF Length([Exclusive Producer Number]) = 9 THEN Left([Exclusive Producer Number],2)+'00'+SubString([Exclusive Producer Number],3,6) ELSE 'NA' ENDIF

This or something very close should do the job,

[jamielaird]

@Thableaus did it better ;)

[Kavya432]

Thank you so much! it worked!

[Kavya432]

Thanks a lot for the solution!