If you are not careful in Excel and you forget to change the formatting of the column with the telephone area code from 'Standard' to 'Text', you will loose the leading zeros. With a program like the DataQualityTools, it is easy to deal with this problem.
To do so, follow the steps below:
- If you have not already done so, download DataQualityTools free of charge here. Install the program and request a trial activation. Then you can work with the program for one whole week without any restrictions.
- The function we require to supplement the leading zeros in the area code can be found in the menu of the 'Add data' block and is called 'Supplementing data fields'.
- After starting this function, the project management appears. Here, you create a new project with a project name of your choice and then click on the 'Next' button.
- In the next step, we start by opening the Excel file containing the area codes to be corrected by clicking on the 'Open file' button. If the Excel file contains more than one worksheet, you also have to select the desired worksheet.
- Afterwards, you have to select the data field you want to process from the list of data fields contained in the Excel file.
- With the 'Next' button, we come to the dialogue where the actual function can be configured. Enter a zero for the 'String to be inserted’. You then select 'Insert in front of the existing field contents'. Finally, you have to set 'Always insert' and the checkmark at 'Only insert when the existing field content not already contains the string to be inserted'.
- By clicking on ‘Next’, you start the function. After it is done, you can verify the results by using the 'Manual post-editing' button.
- In the last step, you have to transfer the results back into the source file. To do so, use the 'Write to source file' button.
- And lo and behold, the missing leading zero is back where it should be.
