Start a new topic

UK Bank Sort Code Reg Ex

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


James,

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
thanks for this - works perfectly!
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
John,

The whole formula is the value to match on.

Can you give an example of your incoming data that isn't working?
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

image

It does appear to be working with that data.  Make sure you don't have any leading or trailing spaces in your entries.

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!

Thanks for getting back to me guys, 

 

Nic, your formulas got me half way there, see below. I will try the other one.

 

image

Unfortunately neither expressions are working for the second part, adding the "-"

Any thoughts guys?
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!

image


Works perfectly, thanks mate!
Login or Signup to post a comment