Forum Discussion
- Will_HMYOB Moderator
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.- PankajSoniContributing Partner
Hi Will_H ,
Thanks a lot.
I really appreciate your help. now it's been solved and working pefectly.
Have a nice day :-)
Pankaj Soni
- Leneth_AMYOB Staff
Hi PankajSoni
We're glad to hear it's been resolved. We're always happy to help you again for future posts,
Cheers,
Leneth
- kelvin_kMYOB Staff
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.