How to clean a phone number list before a campaign

A list collects mistakes over time. Numbers get typed twice, exported from different tools, copied through Excel and mixed with other countries. Sending to a list like that costs money and makes your results harder to read. Here is the order we recommend for cleaning it, from the original export to a list that is ready to use

1. Keep the original

Work on a copy and keep the first export untouched. If something goes wrong halfway, you can always start again from it

2. Put the numbers in one column

Exports from CRMs, shops and forms often split a number over several columns (country code in one, number in another) or put two numbers in one cell. Before anything else, get one complete number per row in a single column

3. Stop Excel from changing the numbers

Excel treats phone numbers as normal numbers, and that causes two problems:

• Scientific notation. A long number like 4915123456789 is shown as 4.91512E+12. If you save the sheet as CSV like this, the file contains exactly that text, and the real number is gone
• Lost zeros. A number typed as 0151… loses its leading 0

To avoid both, set the column to Text before you paste or type numbers into it. When you open a CSV, do not double-click the file. Use Data → From Text/CSV, press Transform Data, and change the type of the phone column to Text. If your sheet already shows E+12, change the column format to Number with 0 decimal places before you save

Tip: Open the saved CSV in Notepad once. What you see there is exactly what you will upload

4. Write every number in international format

Every number needs its country code and no national 0 after it, so 0151 xxxx xxxx becomes 49151xxxxxxxx. If you are not sure how a country writes its numbers, our guide on international format has examples for 20 countries

5. Remove duplicates after formatting

Do this step after step 4, not before. 0151 1234… and +49 151 1234… look different, but they are the same number. Once every number is in the same format, Excel's Data → Remove Duplicates finds them all

6. One country per file

Sort the column so numbers with the same country code sit together, then save each country as its own file. Many tools, including ours, work on one country at a time

7. Remove numbers that cannot be right

A mobile number has a fixed length in each country. Numbers that are much shorter or longer than the rest, text in the number column, and empty lines can go

8. Find out which numbers are reachable

A number in the right format can still be unused, or not on the app you want to use. Before a campaign on WhatsApp, iMessage or RCS, check the list on that channel. With PWF Platform you upload the file and get two files back: numbers that are registered on that service and numbers that are not

9. Keep a "do not contact" list

People who asked you to stop messaging them should never come back through a new import. Keep their numbers in a separate file and remove them from every list before you send, even from lists that were checked before

10. Check again before the next big send

Numbers change owners, people switch apps and accounts get closed. A list that was clean six months ago is not clean today. Write the date of the last check next to each list and check again before larger campaigns

Use it responsibly

Cleaning a list does not give you permission to message the people on it. Only work with numbers you are allowed to process, and follow the consent rules of the countries you send to