Map Extract

Excel removes the leading zero because it treats 01134960000 as a number, and numbers have no leading zeros. To stop it, do not double-click the CSV: import it with Data, From Text/CSV and set the phone column to Text, or switch off Excel's automatic data conversion. To repair a column that is already damaged, rebuild each number in +44 international form with a formula, then keep phones stored that way.

This is one of the Excel problems covered in the B2B lead list cleaning checklist.

Why Excel does it

When you open a CSV by double-clicking it, Excel reads every value with the General format and guesses its type. A CSV has no column types at all; RFC 4180 only defines commas, quotes and line breaks. So Excel sees 07700900123 and decides it is the number 7,700,900,123.

Microsoft's guide to keeping leading zeros describes the related behaviours:

  • Leading zeros are removed from numbers.
  • Excel has "a maximum precision of 15 significant digits"; digits beyond the 15th are changed to zero.
  • Long numbers may be displayed in scientific notation.

For UK lead lists that produces three typical symptoms:

You seeOriginalWhat happened
11349600000113 496 0000 (spaces already removed)Leading zero dropped
770090012307700 900123Leading zero dropped
4.41135E+11+441134960000Plus sign read as "positive", number shown in scientific notation
0113 496 0000 (unchanged)0113 496 0000The spaces made it text, so it survived

Note the last row: numbers with spaces survive because Excel cannot read them as numbers. That is why the problem seems to appear and disappear between files.

Fix 1: import with Power Query and set the column to Text

This works in current Excel for Windows and Microsoft 365.

  1. Open a blank workbook. Do not open the CSV directly.
  2. Go to Data and select From Text/CSV (next to Get Data). Choose the file.
  3. A preview window opens showing the first rows, with a File Origin setting at the top. Choose 65001: Unicode (UTF-8) if business names contain accents or symbols.
  4. Select Transform Data at the bottom of the preview. Older versions label this Edit. The Power Query Editor opens with your columns.
  5. Click the phone column header. Hold Ctrl and click any other columns that must stay exact: postcodes, place IDs, company numbers.
  6. Go to Home, Transform, Data Type and choose Text.
  7. If a Change Column Type dialog asks, choose Replace current. This matters: "Add new step" applies Text after Excel has already converted the values, and the zeros are already gone.
  8. Select Close & Load. The data lands in a new sheet with the zeros intact.

The steps follow Microsoft's own guidance. If you import the same layout often, the query can be refreshed for each new file.

Fix 2: the legacy Text Import Wizard

If you prefer the older wizard, enable it under File, Options, Data, Show legacy data import wizards, then use Data, Get Data, Legacy Wizards, From Text (Legacy). In step 3 of the wizard, click the phone column in the preview and set Column data format to Text. The wizard does not create a link back to the file, which some people prefer for one-off imports.

Fix 3: switch off automatic data conversion

Excel for Microsoft 365 and Excel 2024 let you turn the guessing off. Microsoft's automatic data conversions page gives the path on Windows as File, Options, Data, Automatic Data Conversion (on a Mac, Excel, Preferences, Edit). Clear:

  • Remove leading zeros from numerical text and convert to a number
  • Keep first 15 digits of long numbers and display in scientific notation

With these off, double-clicking a CSV keeps 01134960000 as text. The setting is per computer, so a colleague opening the same file may still see the zeros vanish.

Fix 4: format cells before you type or paste

For numbers typed or pasted by hand, select the column, press Ctrl+1, choose Text on the Number tab, then enter the data. Microsoft notes this only affects values entered after formatting; it will not bring back zeros already removed. A leading apostrophe ('01134960000) also forces text for a single cell.

Avoid the custom format trick (00000000000) for phone numbers. It makes the cell look right but stores a number. The zero is only a display effect, so it is missing in formulas, lookups and any tool that reads the underlying value, and it vanishes again the next time the CSV is opened.

Repair a column that is already damaged

If the zeros are already gone, rebuild the numbers in international form rather than adding the zero back. For a column of UK numbers with no leading zero in A2 downwards:

="+44"&A2

For a mixed column (some with zeros, some without, some with +44, spaces, brackets or (0)), this formula for Excel 365 and 2021 normalises them:

=LET(d,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A2,"0"),"(0)","")," ",""),"(",""),")",""),"-",""),IF(LEFT(d,1)="+",d,IF(LEFT(d,2)="44","+"&d,IF(LEFT(d,1)="0","+44"&MID(d,2,20),"+44"&d))))

Then:

  1. Copy the results and use Paste Special, Values to replace the formulas.
  2. Add a check column: =LEN(B2). Most UK numbers become +44 followed by 10 digits, 13 characters in all. Review anything else by hand; it may be an international number, an extension or a typo.
  3. Only use this on columns you know are UK numbers. A US or Irish number without its country code will be given the wrong prefix.

When repair is not possible. Excel writes CSV values as they are displayed. If a file was saved while numbers showed as 4.41135E+11, the CSV now contains that rounded text and the original digits are gone. Re-export from the source instead.

Store phones as E.164

The lasting fix is to store numbers in E.164 form: a plus sign, the country code, then the national number without its leading zero. The ITU's E.164 recommendation is the international numbering plan behind it, and E.164 numbers are never longer than 15 digits.

Display formatE.164Ofcom range
0113 496 0000+44113496000001 and 02: geographic
07700 900123+44770090012307: mobile
0808 157 0000+448081570000080: free to call

Ofcom's phone numbers and call costs page lists the ranges. Keeping a type column next to the number lets you treat mobiles separately; for a sole trader, the mobile is usually their own phone. Formatting for display (spaces, brackets) belongs in a separate column or in your CRM. The free phone number formatter converts a whole column to E.164.

E.164 does not make Excel safe on its own: +441134960000 can still be read as a number and lose its plus sign, as the table at the top shows. Import as Text either way.

CSV export tips

  • Keep a working copy in XLSX. Excel preserves Text-formatted columns in XLSX; CSV keeps nothing but characters.
  • Check the CSV in a text editor before uploading it to a CRM or sending tool. You should see +441134960000, not 4.41135E+11.
  • Do not add apostrophes or ="..." wrappers to CSV files. They protect the value in Excel but arrive as literal characters in other tools.
  • Save as CSV UTF-8 if names contain accents or the pound sign; see CSV shows é or £ in Excel.
  • Choose the right format for the job. XLSX keeps text columns as text; CSV is portable but carries no types. If you are exporting from a maps tool, how to scrape Google Maps for business leads covers the export settings.

How Map Extract helps

  • Google Maps listings collected by Map Extract keep the phone as shown and a separate E.164 version (for example +441134960000), so there is always a version without a leading zero to lose.
  • The website data extractor saves up to ten phone numbers per business from its website, each with the page it came from, so a damaged number can be traced back and checked.
  • Exports come as CSV, XLSX or JSONL, and CSV can be written as Excel-compatible UTF-8 with a byte order mark. If you work in Excel, use the XLSX export or the import steps above rather than double-clicking a CSV.
  • The workflow builder saves the same sources and order as a repeatable recipe, so each new export has the same columns for a saved Power Query to refresh.

What it does not do: it cannot change how Excel opens a CSV on your computer, and it cannot repair numbers that were damaged in a file after export.

Key takeaways

  • Excel drops the zero because it reads phone numbers as numbers.
  • Import with Data, From Text/CSV and set phone columns to Text with Replace current.
  • Turn off automatic data conversion in Microsoft 365 or Excel 2024 if you open CSVs often.
  • Repair damaged numbers into +44 form with a formula, then check their length.
  • Store numbers as E.164 and check exported CSVs in a text editor.

Frequently asked questions

Why does Excel remove the leading zero from phone numbers?

Excel guesses each value's type when it opens a CSV or when you type into a cell formatted as General. A UK number such as 01134960000 looks like a number, and numbers have no leading zeros, so Excel stores it as 1134960000. Long numbers can also be shown in scientific notation. Importing the column as Text, or switching off automatic data conversion, stops it.

How do I open a CSV in Excel without losing leading zeros?

Do not double-click the file. In Excel, go to Data, From Text/CSV, choose the file, then select Transform Data (Edit in some versions). Select the phone column, set Data Type to Text, choose Replace current, then Close and Load. Do the same for postcodes, IDs and any other column that must keep its exact characters.

How can I add the missing zero back to UK phone numbers?

If the damaged numbers are in column A, a formula such as ="+44"&A2 rebuilds them in international form, which is safer than adding the zero back. For mixed columns, strip spaces and brackets, keep numbers already starting with +, add + to those starting with 44, and replace a leading 0 with +44. Then paste the results as values and check the length.

Why do my phone numbers show as 4.41135E+11 in Excel?

Excel displays large numbers in scientific notation when a column is in General format. The digits are usually still there for a 12-digit UK number, so changing the format to Number with no decimals shows them again. The danger is saving: Excel writes CSV values as displayed, so saving in that state can permanently replace the number with its rounded form.

Sources

  1. Keeping leading zeros and large numbers, Microsoft Support
  2. Set automatic data conversions, Microsoft Support
  3. ITU-T Recommendation E.164
  4. Phone numbers and call costs, Ofcom
  5. RFC 4180: Common Format and MIME Type for CSV Files

Written by the Map Extract editorial team. Found an error? Email support@mapextract.uk and we will check and correct it. How we research guides: editorial policy.

See it working on your own searches.

Start with one keyword, one location and a small limit. Review the records, then decide on a licence.

Top