Im trying to use ImportOMatic to import Direct debits, which is all working fine, except for when we are supplied Sort Codes in the wrong format.
Im trying to figure out how to use Regular Expressions to turn 123456 into 12-34-56, but not having much luck - has anyone else done this?
Also sometimes a sort code will begin with a 0, so if someone opens an import file up in Excel it strips the leading zero off. Is there a way a reg ex ca be used to work out if a string is only 5 numbers in length - i.e. 12345, it should be converted to 01-23-45?
It does appear to be working with that data. Make sure you don't have any leading or trailing spaces in your entries.
Thanks for getting back to me guys,
Nic, your formulas got me half way there, see below. I will try the other one.