Sometimes, when importing UK Bank Account Numbers and Sort Codes, the data source (like a CSV file) will strip out the zeros at the beginning of each of these numbers. This can cause problems when importing.


One way to solve this problem is to go back into the data source and try and re-add them, but that is time consuming and prone to human error.


Instead, why not try this data transformation approach instead that lets Importacular do the hard works by adding the leading zeros during the import stage.


Sort Codes


UK Sort codes are six digits long and can be displayed with or without dashes (40-44-51 or 404451). Some banks and branches have sort codes that start with a zero (04-42-51 or 044251). When a Sort Code is presented in a data source like a CSV file, that starts with zero and has no dashes, it will be presented as just a 5 digit number (44251).


  • To re-add that leading zero in Importacular, head to the field setting for Sort Codes and click on the green plus at the bottom of the Data Transformation section.

  • Next, within the new data transformation row that has been created, add this RegEx formula into the 'From Source' column.

    \b\d{5}\b

  • In the 'Change to Target' column, add a zero.

  • Now set 'Match Type' to Regex and 'Replace Type' to Prepend.

  • Now Save and continue with your import.



Account Numbers


UK Account Numbers are 8 digits long, but can start with several leading zeros. ITo ensure that the right number of zeros are added, we are going to create several Data Transformation, all similar to what we did with Sort Codes, but tailored to add 1, 2 or 3 leading zeros if they are missing.


1st Data Transformation

  • Head to the field setting for Account Numbers and click on the green plus at the bottom of the Data Transformation section. Within the new data transformation row that has been created, add this RegEx formula into the 'From Source' column.

    \b\d{7}\b

  • In the 'Change to Target' column, add a zero.

  • Now set 'Match Type' to Regex and 'Replace Type' to Prepend.


2nd Data Transformation


  • Click the green plus again to add another data transformation row. This time add the adjusted RegEx formula into the 'From Source' column.

    \b\d{6}\b

  • In the 'Change to Target' column, add two zeros.

  • Now set 'Match Type' to Regex and 'Replace Type' to Prepend.


3rd Data Transformation

  • Finally, click the green plus again to add a third data transformation row. This time add another adjusted RegEx formula into the 'From Source' column.

    \b\d{5}\b

  • In the 'Change to Target' column, add three zeros.

  • Now set 'Match Type' to Regex and 'Replace Type' to Prepend.
  • Now Save and continue with your import.




What is the RegEx Formula and Data Transformation doing?


For those that are curious, the RegEx formula is looking to the length of the data in the data source and, in the case of the the Sort Codes, if it is 5 digits long (indicated by the {5}), it offers it up for the data transformation to add a zero to the beginning of the 5 digits (the prepend).


In the case of the Account Numbers, the formula is looking to see if the source data is 7, 6 or 5 digits long ({7}, {6}, {5}) and adding 0, 00 or 000 to the beginning of the source data


Can you Zeidman help with other Regular Expressions?


We are afraid that we are not able to support the writing of Regular Expressions. We can only show you how a regular expression could be used with Data Transformations. For more information about regular expression, please look to this Knowledge Base article:


What support is available for Regular Expressions?


Other Related Articles:


There are other areas in Importacular where we have built in functionality to cater for specific leading zeros issues. 


Correct US Zip Code Missing Leading Zeros


Correct Telephone Numbers Missing Leading Zeros