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

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

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!

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

Any thoughts guys?

Works perfectly, thanks mate!