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.

Hi Pankaj,

 

Thank you for your post. As you are listed as an MYOB Advanced Partner, please post this on the Partner Community Forum.

 

I will send you a private message with an email address if you have not already been registered into this private forum.