Data Cleanup

In some cases you will need to clean up your data before importing it into Noojee Campaigner.

One of the most common issues is that phone numbers can be stored an a wide variety of formats.

Note: we will shortly have a release of Noojee Campaigner that cleans phone numbers automatically.

The simplest way of resolving this issue is to use Excel (or Open Office Calc) to remove the odd characters.

The following formula will remove spaces,  brackets, commas and decimal points from within a phone number.

You can easily expand the set of characters that are removed by extending the following formula with additional 'substitute' commands.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, ".", ""), ",", ""), "(", ""), ")", ""), " ", "")

To use the above formula, open your CSV file that contains the phone numbers which need to be cleaned up, using Excel or Open Office Calc.

You will first need to save your CSV file as an Excel spreadsheet as CSV file doesn't support formulas.

Insert a new column just after the column containing your phone numbers.

Inset the above formula into the 2nd row of the new column (assuming that your first row contains the required heading labels). 

Replacing the value 'A1' in the above formula with a reference to the first column/row that contains a phone number. If your phone column is the first column in your spreadsheet then A1 will actually be the correct value.

Now copy the column that contains the substitute formula down the entire spreadsheet so every row as the formula.

You now see a clean version of your phone number in the new column.

Now save the file to a new CSV file and the re-open it. NOTE: this will cause the above formula to be lost but the cleaned number to be kept.

You now have a CSV file with two columns that contain a phone number. You need to delete the old column, change the heading of the new column so that it is called 'phone' and save the results back to the CSV file.

You are now ready to import the CSV file into Noojee Campaigner.