How to Format Phone Numbers in Excel for CRM Imports

Why does Excel change phone numbers?

A phone number is an identifier, not a quantity. When Excel interprets it as a number or formula, it can remove information you wanted to keep.

The plus sign can disappear

When typed into a General cell, a leading + can be interpreted as the start of a formula. For example, Excel may store +442079460018 as a numeric value without the plus sign. Format the column as Text before pasting, or enter an apostrophe first: '+442079460018. The apostrophe is an entry instruction, not part of the stored text.

Leading zeros can be lost

If Excel stores 02079460018 as a number, the initial zero disappears. Changing the cell to Text afterwards does not recover it. Start from the original source where possible; do not add zeros to every row without checking each country’s rules.

Scientific notation is not always data loss

A value such as 4.42079E+11 may just be a display choice. Widen the column or inspect the formula bar to see the stored value. Excel’s numeric precision limit is 15 significant digits; longer numeric identifiers can lose trailing digits. Displaying a number in scientific notation alone does not prove that its digits have changed.

The safest import workflow

1. Choose Data → From Text/CSV rather than opening a CSV by double-clicking it.
2. Choose Transform Data and set the phone column’s data type to Text. Check the applied steps so an earlier numeric conversion has not already removed zeros.
3. Inspect a few values with plus signs and leading zeros, then load the data.

Microsoft explains the text-import approach and the precision limit in its guide to keeping leading zeros and large numbers.

How do custom number formats work?

Select a numeric phone column, press Ctrl+1, choose Custom and enter 000-000-0000. A ten-digit value such as 2025550123 will display as 202-555-0123. The underlying value remains numeric.

This is useful for a worksheet containing one known national format. It becomes fragile when rows have different lengths, country codes or extensions. There is no single grouping pattern that correctly handles every international number.

When should I use the TEXT function?

Use TEXT when you have a consistent numeric pattern and need a new text column. For a known ten-digit national number in A2:

=TEXT(A2,"000-000-0000")

This produces a text value such as 202-555-0123. Copy the results and use Paste Special → Values if you want to remove the formulas before exporting.

The pattern only describes appearance. It does not identify the country, check whether the number is valid, or determine which leading zeros belong to the number. A known fixed-width pattern can pad zeros, but it cannot establish whether those zeros are correct.

For Excel installations that use semicolons as argument separators, replace formula argument commas with semicolons. The examples here use English function names.

How do I convert phone numbers to E.164 in Excel?

For contact storage, E.164 is commonly represented as a plus sign followed by the country calling code and national significant number, with no spaces or punctuation. Extensions stay separate. The international number contains at most 15 digits; the plus sign is not a digit.

For example, a UK national number 020 7946 0018 becomes +442079460018 when the UK trunk prefix is handled correctly. See the international phone-number format guide for other output formats.

Step 1: remove simple presentation characters

For a plain phone value in A2, this helper formula removes spaces, dashes and both parentheses:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"(",""),")","")

Apply it to every row, including values that already start with +. It does not handle every separator, non-breaking spaces, extensions or multiple numbers in one cell. Review those cases separately.

Step 2: confirm the country and national number

If the cleaned number has no international prefix, obtain the country from a reliable source column or the contact record. Handle the country’s trunk-prefix rules before adding the calling code. Many countries drop a national trunk zero, but some zeros are significant: never apply one universal “remove the first zero” rule.

Step 3: combine only verified components

If B2 contains the cleaned national significant number, and C2 contains the verified country calling code without a plus sign, you can combine them:

=IF(B2="","",IF(LEFT(B2,1)="+",B2,"+"&C2&B2))

This formula assumes that you have already handled national trunk prefixes and international dialling prefixes. It does not validate the result. For example, the verified components 2079460018 and 44 give +442079460018.

Want to avoid maintaining these formulas? Try the same sample in E164flow. Choose a default country or map a country column, then review the formatted numbers and warnings together. You keep the original inputs for comparison instead of replacing them with a formula result.

How should I handle mixed-country lists?

Separate explicit international numbers from national numbers before you transform them. Keep a copy of every original value so you can trace a change.

Where Power Query helps

Power Query is useful for a repeatable workflow: import the phone column as text, trim unwanted characters, join a trusted country mapping and flag missing country values. A country-code lookup is only one part of the job; it does not supply national numbering rules.

Do not globally replace every 00 inside a number with +. Only transform an international prefix at the start, after confirming the source convention. For mixed-country parsing, use numbering metadata rather than a growing collection of ad hoc substitutions.

How can I validate and review the results?

Start with empty values, unexpected characters, extensions and unusually short or long results. A length check can catch obvious mistakes, but national lengths vary. Do not use an arbitrary eight-digit minimum as a universal validity rule.

Excel’s numeric coercion tests are not phone validators: they may accept signs, decimals or scientific notation. A more useful review separates three questions:

1. Structure: does the result have the intended international representation, with a country calling code and digits only after the plus?
2. Numbering rules: does it match the length and prefix rules for that country?
3. Real-world status: is it active, reachable and associated with the intended contact?

A library such as libphonenumber supports the second question using numbering metadata. It does not confirm the third. A plausible or valid-looking number can still contain the wrong digits.

Keep CRM matching separate from formatting

Consistent numbers make comparisons easier, but E.164 does not determine how a CRM identifies a contact. HubSpot automatically deduplicates contacts by email; import updates can also use Record ID or supported unique properties. Do not assume that matching phone values will merge records.

For HubSpot or Pipedrive, check the current import and duplicate-handling settings, retain the appropriate record identifier, and import a small sample first. See HubSpot’s deduplication documentation for its specific rules.

Which method should I choose?

Excel can handle substantial lists. The risk is often the transformation logic and missing source information, rather than the number of rows alone. Make the workflow repeatable and preserve original values for review.

When should I use E164flow instead of formulas?

E164flow is useful when you want to choose phone columns, apply country-aware formatting, review warnings and duplicates, and export the results without building that workflow in Excel. It uses libphonenumber-js numbering metadata, with a default country or mapped country column for national numbers.

For a one-off display change, Excel may be sufficient. For repeated international phone cleanup, our recommendation is to start with E164flow: it gives you the parsing and review workflow without building each step yourself.

Contact files are processed locally in your browser and are not uploaded. Account and subscription information is handled separately; see our privacy page. Local processing does not by itself establish compliance for every use of your contact data.

The converter supports CSV/XLSX input, multiple phone columns, separate extensions and duplicate references. Review Ready, Needs review and Invalid results before exporting. It does not verify that numbers are active, establish ownership, supply contact consent, or automatically merge records in your CRM.

Try up to 25 pasted numbers without an account. A free account adds file input and export for up to 50 rows. Pro supports up to 20,000 rows per file. See the current plans for details.

For the final handoff, XLSX text cells preserve plus signs and zeros. If you choose CRM CSV, import it directly into the destination rather than opening and resaving it in Excel. Our CSV vs XLSX guide explains the export choices.