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?
These two should do the trick and they need to be placed in this order.
Replacement Value: 0$1 Value to match on: (^\d{5}$)
Replacement Value: $1-$2-$3 Value to match on: (^\d{2}(?=\d{4}$))((? The first set adds the leading zero if it is missing and the 2nd set formats the sort code if it is in a 123456 format
J
James Measey
said
over 7 years ago
thanks for this - works perfectly!
J
John Drake
said
over 5 years ago
Hi Nic,
I am not having much success with these Regular Expressions,
I was wondering , for the second replacement value that populates "-" between digits does the whole formula (^\d{2}(?=\d{4}$))((? Thanks John
N
Nic Bourne
said
over 5 years ago
John,
The whole formula is the value to match on.
Can you give an example of your incoming data that isn't working?
J
John Drake
said
over 5 years ago
Hi Nic,
Here are two examples of Data I am trying to bring in,
50676 - missing a leading zero 207501 - requiring "-" between two digits. Thanks John
N
Nic Bourne
said
over 5 years ago
It does appear to be working with that data. Make sure you don't have any leading or trailing spaces in your entries.
W
Wayne Pozzar
said
over 5 years ago
A slightly different way to do this would be (in this order)
Value: 0$1 Match: ^(\d{5})$
Value: $1-$2-$3 Match: ^(\d{2})(\d{2})(\d{2})$
Make sure to check the "Use Regular Expressions" at the bottom!
J
John Drake
said
over 5 years ago
Thanks for getting back to me guys,
Nic, your formulas got me half way there, see below. I will try the other one.
J
John Drake
said
over 5 years ago
Unfortunately neither expressions are working for the second part, adding the "-"
Any thoughts guys?
W
Wayne Pozzar
said
over 5 years ago
I noticed when trying to copy out the formulas from the page it tried to add a space at the end. Make sure there are no spaces anywhere!
James Measey
Hi,
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?
Thanks
james