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_AFormer 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.
Looking for something else?
Search the Community Forum for answers or find your topic and get the conversation started!
Find technical support and help for all MYOB products in our online help centre
Dig into MYOB Academy for free courses, learning paths and live events to help build your business with MYOB.