Keep phone numbers as text in CSV to Excel

Preserve leading zeros, plus signs and extensions so dialing strings stay literal.

Phone numbers look numeric but must stay text

A phone field can start with +, 0, spaces, parentheses or an extension marker. Spreadsheets commonly drop a leading zero on national numbers, turn +1 into a formula-like token, or store the value in scientific notation when the digit string is long. The teaching example keeps an E.164-style number, a zero-padded national number and an extension so you can verify each form survives the handoff.

contact_id,phone_e164,phone_national,extension
C-001,+12025550127,0205550127,101
C-002,+442079460958,02079460958,
C-003,+81312345678,0312345678,22

These values are synthetic. They are not real customer numbers. The point is structure: plus signs, leading zeros and short extensions are part of the identifier, not quantities to sum. Download /samples/phone-numbers.csv for the same bytes.

Before and after: Excel versus text columns

A general-format open of the national column often looks like this:

contact_id  phone_e164      phone_national  extension
C-001       #NAME? or text  205550127       101
C-002       …               2079460958
C-003       …               312345678       22

Leading zeros disappear; a leading + may become an error or a formula. A text-preserving XLSX keeps the dialing strings literal:

contact_id  phone_e164       phone_national  extension
C-001       +12025550127     0205550127      101
C-002       +442079460958    02079460958
C-003       +81312345678     0312345678      22

Compare the formula bar, not only the column width. A custom format that “adds” a zero only changes appearance.

Steps that preserve the dialing string

1. Start from the CRM or PBX CSV, not from a workbook that already rewrote the column.
2. Load the file in CSV to Excel with Comma and a header.
3. Preview. Confirm +12025550127, 0205550127 and the extension still match the source.
4. Keep every phone column on Text. Download XLSX and compare a few rows in the formula bar.

Keep phone fields as text →

Step-by-step troubleshooting

1. National number already shorter in the CSV. Re-export from the CRM. TableMender cannot invent a discarded zero.
2. Plus sign triggers a formula warning. That is expected in some spreadsheets when the cell is not text. Keep the column on Text in TableMender; do not “fix” by deleting the plus.
3. Long digit strings show as 1.2E+10. Same injury as tracking IDs — see scientific notation. Leave the column on Text.
4. Duplicate contacts share a phone. Deduplicate with an explicit key in CSV clean only after the phone strings still match the source.

What goes wrong in a general-format open

A national number that begins with zero becomes a shorter integer. A value that begins with + can be treated as formula-like. A long digit string can appear as an exponent. If the CSV already lost the zero, local conversion cannot invent it.

Honest limits

No carrier lookup, no libphonenumber formatting, no SMS gateway. Parentheses and spaces are preserved only if they are still in the source file. Extensions in a separate column are safer than free-text “x101” glued to the number when some rows lack an extension. Do not use Number for phone columns even when every row happens to be digits: tomorrow’s row may need a leading zero. Capacity limits (5 MiB, 20,000 records) apply like every other tool on this site.

Worked cells you can check in the converter

Load phone-numbers.csv or the “Phone numbers” sample pack entry in CSV to Excel. After Preview, the local preflight should warn on plus-prefixed values and leading zeros. Leave phone_e164, phone_national and extension on Text. Choosing Number for phone_e164 is blocked because of the plus sign; choosing Number for phone_national is blocked because of the leading zero. That block is the tool working, not a parse failure.

Compare the formula bar in the downloaded workbook: +12025550127 still starts with a plus, 0205550127 still starts with zero, and the empty extension on C-002 stays blank rather than becoming a numeric zero. Formula protection on a CSV export may prefix the plus with an apostrophe; that is an intentional character change, separate from Excel column types.

Extensions, grouping punctuation and international prefixes

An extension stored as 0205550127 x101 in one cell is still text, but it is a poor key for later matching. Split extensions when the source system can. Parentheses and spaces such as (020) 555 0127 survive only if they are still in the CSV; a previous spreadsheet that stored a number will have dropped them. A trunk prefix written 0044 instead of +44 is another leading-zero case — keep it on Text; do not “normalize” it in Excel by multiplying by one.

WhatsApp and CRM exports sometimes wrap the number in a formula-like + that spreadsheets execute. TableMender’s XLSX never creates formulas; Text stores the plus as a character. CSV without protection can still be executed by the next spreadsheet that opens it. Turn protection on only when that destination needs the apostrophe, and compare a plus-prefixed row before overwriting the CRM file.

FAQ grounded in CSV to Excel

Can I mark a phone column as Number if every current row is digits? You can, and it is usually a mistake. The next row with a leading zero or a plus will block Number export or lose the prefix. Leave the column on Text.

Does the tool validate E.164? No. It does not check country codes, lengths or check digits. A well-formed-looking teaching row is not a certified number.

Should I dedupe on phone? Only with an explicit key after the strings still match the source. Two contacts can share a switchboard number. See duplicate keys.

Number export on phone-numbers.csv

Load phone-numbers.csv in CSV to Excel with Comma and a header. Set one column to Number, read the list, then set it back to Text. The header is source record 1. C-001 is source record 2.

Preflight on phone-numbers.csv

With every column on Text, the identifier row warns: leading zeros in column 3 (3 cells) and plus-prefixed dialing strings in column 2 (3 cells). The how-to link on that row is this page. There is no 16-digit token and no ZIP+4 shape, so the link does not jump to the long-ID or ZIP guides.

The formula-like row stays clear. Plus-prefixed phones are counted on the identifier row and are left out of the formula-like count. Excel can still treat a leading + as a formula when the cell is not text. XLSX Text stores the plus as a character. CSV formula protection prefixes an apostrophe and changes the exported characters. Leave protection off unless the next spreadsheet would execute the field, and compare one plus-prefixed row before you replace the CRM file.

The duplicate-row check skips C-002 because the extension is empty. It reports no exact whole-row duplicates among complete rows, and one incomplete row skipped. That sentence does not mean the three dialing strings are safe dedupe keys. Empty-record removal removes nothing: a blank extension is not an empty record. Two contacts can still share a switchboard number. See duplicate keys.

Related guides and tools

For related padding injuries see leading zeros, SKU codes and ZIP+4. Long digit strings without a plus belong with long IDs as text. Matching tools: CSV to Excel, CSV clean, CSV check & repair.

← All guides