WhatsApp Number Format and Country Code: Cleaning Faulty Numbers in Excel
Do you know what percentage of the numbers in your campaign list are actually written correctly? In a customer list pasted into Excel, the same person can appear in four different formats: 0532 123 45 67, 5321234567, +90 (532) 123-45-67, and 5,32E+11. Because of these differences in writing, WhatsApp cannot find the number, messages do not go out, and the error report grows. The good news is that the problem is usually solved with a few simple rules and a few Excel formulas. In this article we explain the number format WhatsApp expects, country codes, common mistakes, and the steps to clean up your list.
The Format WhatsApp Expects
WhatsApp recognizes numbers in international format: the country code, followed by the national number without a leading zero. A plus sign, spaces, brackets, and dashes are not needed; digits alone are enough.
| Local format (Türkiye) | WhatsApp format | Explanation |
|---|---|---|
| 0532 123 45 67 | 905321234567 | The leading 0 is removed, 90 is added, spaces are deleted |
| 532 123 45 67 | 905321234567 | The country code is added |
| +90 532 123 45 67 | 905321234567 | The plus sign and spaces are deleted |
| 0090 532 123 45 67 | 905321234567 | 00, the international dialing prefix, is deleted |
| (0532) 123-45-67 | 905321234567 | Brackets and dashes are deleted |
The number in the example is fictional. In Türkiye, mobile phone numbers consist of 12 digits in total including the country code, and start with 905.
Why the Leading Zero Matters
Our local dialing habit is to put a zero at the start of the number; in international format, this zero is not included. If you write 9005321234567 instead of 905321234567, the number is invalid. The same rule applies in many countries: the local "trunk" zero is dropped together with the country code. In the United Kingdom, the number 07911 123456 becomes 447911123456, not 4407911123456.
Commonly Used Country Codes
| Country | Code | Local example | WhatsApp format |
|---|---|---|---|
| Türkiye | 90 | 0532 000 00 00 | 905320000000 |
| Germany | 49 | 0151 0000 0000 | 4915100000000 |
| United Kingdom | 44 | 07911 000000 | 447911000000 |
| United States and Canada | 1 | (212) 555-0100 | 12125550100 |
| Azerbaijan | 994 | 050 000 00 00 | 994500000000 |
| Netherlands | 31 | 06 0000 0000 | 31600000000 |
| France | 33 | 06 00 00 00 00 | 33600000000 |
| United Arab Emirates | 971 | 050 000 0000 | 971500000000 |
The numbers in the table are representative. In some countries (for example, Mexico and Argentina), additional prefixes may be needed for mobile numbers; if you have many overseas customers, check the current numbering rules of the relevant country.
Common Mistakes in Excel
| Mistake | How it looks | Solution |
|---|---|---|
| Scientific notation | 5,32E+11 | Set the column to the "Text" format and re-enter the number; corrupted digits do not come back |
| Leading zero disappears | 5321234567 | Not a problem; 90 is added to the 10-digit Turkish number |
| Spaces and dashes | 532 123 45 67 | Use a cleaning formula |
| Several numbers in one cell | 532..., 533... | Split them into separate rows |
| Landline | 0212 000 00 00 | May not be registered on WhatsApp; keep them separate |
| Missing or extra digits | 53212345 | Remove from the file or confirm with the customer |
| Cell containing letters | 0532 xxx xx xx | Delete the row |
Numbers that turn into scientific notation are especially dangerous: Excel rounds them, so the last digits are permanently lost. For this reason, set the number column to the Text format before pasting data.
Step-by-Step Cleanup in Excel
- Make a copy of the file. Do not damage the original.
- Select the number column and set the cell format to Text.
- Open a new column next to it and clean out spaces, dashes, brackets, and plus signs. An example formula in Turkish-language Excel:
=YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(A2;" ";"");"-";"");"(";"");")";"");"+";"") - Handle the "00" prefix and the leading zero, and add the Türkiye code:
=EĞER(SOLDAN(B2;2)="90";B2;EĞER(SOLDAN(B2;1)="0";"90"&PARÇAAL(B2;2;20);EĞER(UZUNLUK(B2)=10;"90"&B2;B2))) - Check whether it is a Turkish mobile phone:
=VE(SOLDAN(C2;3)="905";UZUNLUK(C2)=12). Rows that are not TRUE need review. - Copy the result column and fix it with "Paste Values"; then delete the formulas.
- Data > Remove Duplicates to delete repeats.
The English-language Excel equivalents of the formulas are SUBSTITUTE, IF, LEFT, MID, LEN, and AND, and the argument separator may be a comma instead of a semicolon. The formula above is not suitable for foreign numbers; sort those on a separate sheet according to their country code.
What WapFlow Does and Does Not Do
When importing Excel, WapFlow corrects common formats itself: the leading 0 is converted to the Türkiye code, a 10-digit Turkish number is completed with 90, and spaces are removed. For foreign numbers, however, you must type the country code in full yourself; removing short or incomplete numbers from the file is also your responsibility. You can find the import steps in our Excel import guide.
After importing, carry out two checks:
- In the address book, open a few random contacts and check that the number is saved in the right field and with the right digits.
- Run the WhatsApp filter on the Verifier page to find out whether the number is registered on WhatsApp. Numbers with the correct format but no WhatsApp account are separated at this stage. For details, see our number verifier article.
Verification is not 100% accurate; the result depends on WhatsApp's response at that moment. Recheck important lists before a campaign.
Example: Cleaning a 500-Row List
The table below is a fiction, inspired by how a typical cleanup flow might proceed, not based on a real customer. The figures are only meant to show the logic.
| Step | Rows remaining | What was done |
|---|---|---|
| Starting point | 500 | A list combined from different sources |
| Duplicates deleted | 455 | The same person had been entered several times |
| Letters and empty cells removed | 440 | Rows with "ask later" notes |
| Format corrected | 440 | Converted to 12 digits with formulas |
| Landlines separated | 395 | Numbers starting with 0212 and 0216 were moved to a separate sheet |
| Passed through the verifier | 350 | Those without WhatsApp were removed |
In the end, you are left with a smaller but genuinely reachable list. The response rate you measure on this list will also be more meaningful. In addition, noting how much the list shrinks at each stage allows you to prevent errors at the source in your next collection process.
Prevent Errors at the Source
Cleaning up treats the symptom; the real solution is to collect numbers in the right format. Wherever you collect customer information, add small rules:
- Web form: Use a field that asks the customer to select a country code. Limit the number field so that it accepts digits only.
- Appointment book and shop register: When writing numbers, set a single format, for example "05xx xxx xx xx," and make sure everyone uses the same format.
- Read it back to the customer: "Your number is 0532 123 45 67, is that correct?" A simple confirmation prevents many errors.
- Verification: Run newly received numbers through the Verifier once a month; old lists deteriorate over time.
- Permission record: Write next to the number the purpose for which you obtained it and the date of permission; this information is very useful later.
The Same Rule When Connecting Your Own Number
When connecting your own number to the panel, also remember the country code. If you connect using a pairing code, enter your phone number with the country code, without a plus sign and without a zero. With the QR code method, you do not need to enter a number. For the connection steps, see our QR code guide.
List Cleanup Checklist
| Check | Yes |
|---|---|
| Is the number column in Text format? | ☐ |
| Is there any number that has turned into scientific notation? (There should not be.) | ☐ |
| Do all numbers start with the country code? | ☐ |
| Do Turkish mobile numbers have 12 digits and start with 905? | ☐ |
| Have duplicates been deleted? | ☐ |
| Have landlines been separated? | ☐ |
| Has it been passed through the Verifier? | ☐ |
Frequently Asked Questions
Should I put a plus sign at the start of the number?
You do not need a plus sign in Excel files; just type the digits. A plus sign can cause problems in a cell.
Do WhatsApp messages get delivered to landline numbers?
Some landlines may be registered with WhatsApp Business, but in general most landline numbers do not have a WhatsApp account. Check with the Verifier.
How should I write to overseas customers?
Add the full country code and drop the local zero. If the country's numbering rules are different, do a small test first.
What should I do if one person has two numbers?
Use the Verifier to check which one is registered on WhatsApp and use only that one. Sending the same message to both numbers may annoy the person. Note the other number in the contact note.
I wrote the number correctly but the message was not delivered. Why?
The number may not be registered on WhatsApp, the person may have blocked you, or the account may not have been connected at that moment. Check the cause of the error in the campaign report.
After cleaning up your number list, you can use WapFlow to manage the address book, verification, and campaign steps from a single panel.
