Forum Discussion
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 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_A2 years agoFormer 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- PankajSoni2 years agoContributing Partner
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.