Article summary
Clean WA/WS phone numbers, handle country codes and hidden characters, deduplicate after normalization, export TXT, and reconcile the resulting Excel.
WhatsApp number cleaning is more than removing spaces. The same phone can appear as a domestic number, +country-code form, bare country code, or a value with notes. Spreadsheets can also turn long phone strings into scientific notation. The wrong order distorts both deduplication and downstream checks.
This is an operational guide for export, data, and CRM teams. It improves format and duplicate control without claiming that a clean number creates contact permission or higher conversion.
Direct answer: Preserve the raw column, create a normalized candidate from explicit country evidence, deduplicate on that value, and export UTF-8 TXT. Route uncertain records to exceptions instead of guessing.
What cleaning fixes—and what it cannot fix
Cleaning addresses punctuation, domestic prefixes, country codes, duplicate representations, invisible characters, and file structure. Deduplication reduces repeated processing of one normalized phone.
It does not verify ownership, prove WhatsApp registration, create permission, or guarantee delivery. The team still has to choose a defined WS Registration, Activity, Picture, or other task.
| Business question | Evidence required | Role of this workflow |
|---|---|---|
| Can format enter the task? | Normalization and exception rules | Improved by cleaning |
| Is the phone duplicated? | Normalized key | Can be identified |
| Is it on WhatsApp? | WS Registration Check | Not answered by cleaning |
| May we contact it? | Permission and opt-out | Not answered by cleaning |
Freeze these fields before transforming anything
| Object | Ready state | Exception action |
|---|---|---|
| Raw phone | Preserved as text | Never overwrite |
| Country/region | From a reliable source | Do not guess when absent |
| CRM ID | Unique per source record | Create temporary row ID if needed |
| Permission/opt-out | Determines eligibility | Never replace with check output |
A useful worksheet contains row_id, raw_phone, source_country, normalized_phone, exception_reason, and duplicate_group so every transform has a visible input and output.
Eight steps to normalize and deduplicate
- Step 1: Copy a working file
Keep the source export read-only. - Step 2: Force text type
Prevent scientific notation and precision loss. - Step 3: Normalize characters
Convert full-width digits and remove invisible spaces. - Step 4: Remove display punctuation
Strip spaces, brackets, hyphens, and notes. - Step 5: Build international form
Use country evidence for code and domestic prefix. - Step 6: Assign exception reasons
Do not force-repair short codes, extensions, or unknown countries. - Step 7: Deduplicate normalized values
Retain duplicate group and all CRM IDs. - Step 8: Export and sample TXT
UTF-8, one phone per line, no header.
AppShai tasks accept UTF-8 TXT with one phone number per line; Excel is the completed task’s export. Do not place names, email addresses, companies, notes, or a header in TXT, and do not submit CSV or XLSX as the upload file.
Keep preparation and WS outputs in separate fields
| Export field | Meaning | Recommended destination | Does not establish |
|---|---|---|---|
| raw_phone | Source list | raw_phone | Normalized value |
| normalized_phone | Cleaning process | phone_e164_candidate | Registration status |
| duplicate_group | Deduplication | phone_duplicate_group | Customer merge |
| Registration status | WS Registration Excel | wa_registered | Marketing permission |
| Activity time/days | WS Activity Excel | wa_activity_* | Last reply |
The label E.164 candidate is safer than valid phone: a plausible international structure does not prove telecom or platform state.
High-impact data damage to catch
| Failure symptom | Likely cause | Investigation order |
|---|---|---|
| Trailing digits become zero | Spreadsheet precision loss | Return to text source |
| Country code duplicated | Prepended to international form | Rebuild with country-aware rules |
| Domestic zero removed wrongly | Country rule unverified | Restore raw and sample |
| Customers disappear after dedupe | No duplicate mapping retained | Restore duplicate_group |
| Blank rows reach task | Line-break/invisible character issue | Count nonblank lines and re-export |
Keep an unexplained blank or missing row as unknown or pending review. Do not fill gaps with “no,” “not registered,” or zero merely to complete a report. Link the raw TXT, exported Excel, and exception file through the same task ID.
Measure cleaning quality
| Acceptance measure | Method | Purpose |
|---|---|---|
| Source rows | Rows in frozen export | Reconciliation base |
| Normalized successes | Rows with candidate international form | Assess processable scope |
| Unique phones | Count after normalization | Task input size |
| Exceptions | Unknown country, short, damaged, etc. | Manual queue |
| Duplicate reduction | Normalized successes – unique phones | Calculate actual effect |
Use actual duplicate reduction and current task pricing to calculate savings; do not promise a fixed 15%. Deduplicating task rows must not automatically merge several CRM contacts that share a phone.
Privacy and version control for cleaning files
A working file should keep only fields required for the task. Names, emails, companies, and notes do not enter TXT. Include date, source, and version in filenames and protect exception and mapping files as carefully as the raw export.
| Data layer | Owner | Must preserve | Must not overwrite |
|---|---|---|---|
| Source list | Business owner | Source, purpose, permission, CRM ID | Check result |
| Normalized file | Data operator | Raw phone, normalized phone, exception reason | Source record |
| Task snapshot | Task operator | Task ID, date, service, input/output counts | Previous result |
| CRM import | System admin | Field dictionary, import batch, rollback file | Permission and lifecycle stage |
Reusable TXT delivery standard
- UTF-8 encoding.
- One normalized phone per line.
- No header, blank row, or note.
- Consistent plus and country-code convention.
- No non-digit characters except an allowed leading plus.
- Line count equals normalized unique phones.
- Every sample traces to raw_phone and CRM ID.
After preparation, choose the task that matches the question on the AppShai WhatsApp (WA/WS) Number Checker. Separating cleaning, platform checking, and permission prevents one result from being stretched into another claim.
Frequently asked questions
Should I deduplicate before adding country codes?
Normalize from explicit country evidence first, then deduplicate. Otherwise the domestic and international forms of one phone remain separate.
Can TXT keep a leading plus?
Use a consistent international representation and confirm the current task with a sample. The essential rule is one phone per line with no extra text.
Is a cleaned phone a valid customer?
No. Cleaning makes the format processable. Registration, activity, permission, and customer value require separate evidence.
