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

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

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?

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

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!

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.

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

Any thoughts guys?

Any thoughts guys?

Works perfectly, thanks mate!

Help Desk Software
by Freshdesk

## 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