How to add supplier bank account no in same format based on the supplier master--> Payment instruction

Hi PankajSoni ,

I don't know how to do this properly, but the below formula achieves it:

=IIF(LEN([Bankaccountnumber.DetailValue])=16,Concat(LEFT([Bankaccountnumber.DetailValue],2),'-',SUBSTRING([Bankaccountnumber.DetailValue],3,4),'-',SUBSTRING([Bankaccountnumber.DetailValue],7,7),'-',RIGHT([Bankaccountnumber.DetailValue],3)),[Bankaccountnumber.DetailValue])

Notes on the above:

IIF checks the Length of the bank account number, if length is exactly 16 characters (NZ Bank account number true format) then format it.

CONCATENATE (combine strings)

LEFT (first 4 characters), SUBSTRING to grab each of the other 2 segments(4, then 7), RIGHT (last 3 characters)

Finally if the length _was not_ exactly 16 characters, display whatever was in the field.

Edited:
Edited to correct error with substring start positions, thanks to PankajSoni  for providing the correction.