Keeping leading zeros and large numbers

Do you ever import or enter data in Excel that contains leading zeros, like 00123, or large numbers like 1234 5678 9087 6543? Examples of these are social security numbers, phone numbers, credit card numbers, product codes, account numbers, or postal codes. Excel automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15, in order to allow formulas and math operations to work on them. This article deals with how to keep your data in its original format, which Excel treats as text.

Convert numbers to text when you import text data

Newer versions Office 2010 – 2013



Use Excel's Get & Transform (Power Query) experience to format individual columns as text when you import data. In this case, we're importing a text file, but the data transformation steps are the same for data imported from other sources, such as XML, Web, JSON, etc.

1. Click the Data tab, then From Text/CSV next to the Get Data button. If you don't see the Get Data button, go to New Query > From File > From Text and browse to your text file, then press Import.

2. Excel will load your data into a preview pane. Press Edit in the preview pane to load the Query Editor.

3. If any columns need to be converted to text, select the column to convert by clicking on the column header, then go to Home > Transform > Data Type > select Text.

   

   Tip: You can select multiple columns with Ctrl+Left-Click.

4. Next, click Replace Current in the Change Column Type dialog, and Excel will convert the selected columns to text.

   

5. When you're done, Click Close & Load, and Excel will return the query data to your worksheet.

   If your data changes in the future, you can go to Data > Refresh, and Excel will automatically update your data, and apply your transformations for you.

In Excel 2010 and 2013, there are two methods of importing text files and converting numbers to text. The recommended method is to use Power Query, which is available if you download the Power Query add-in. If you can't download the Power Query add-in, you can use the Text Import Wizard. In this case, we're importing a text file, but the data transformation steps are the same for data imported from other sources, such as XML, Web, JSON, etc.

1. Click the Power Query tab in the Ribbon, then select Get External Data > From Text.

2. Excel will load your data into a preview pane. Press Edit in the preview pane to load the Query Editor.

3. If any columns need to be converted to text, select the column to convert by clicking on the column header, then go to Home > Transform > Data Type > select Text.

   

   Tip: You can select multiple columns with Ctrl+Left-Click.

4. Next, click Replace Current in the Change Column Type dialog, and Excel will convert the selected columns to text.

   

5. When you're done, Click Close & Load, and Excel will return the query data to your worksheet.

   If your data changes in the future, you can go to Data > Refresh, and Excel will automatically update your data, and apply your transformations for you.

Use a custom format to keep the leading zeros

If you want to resolve the issue just within the workbook because it's not used by other programs as a data source, you can use a custom or a special format to keep the leading zeros. This works for number codes that contain fewer than 16 digits. In addition, you can format your number codes with dashes or other punctuation marks. For example, to make a phone number more readable, you can add a dash between the international code, the country/region code, the area code, the prefix, and the last few numbers.

Number code	Example	Custom number format
Social security	012345678	000-00-0000  012-34-5678
Phone	0012345556789	00-0-000-000-0000  00-1-234-555-6789
Postal code	00123	00000  00123

Steps    

1. Select the cell or range of cells that you want to format.

2. Press Ctrl+1 to load the Format Cells dialog.

3. Select the Number tab, then in the Category list, click Custom and then, in the Type box, type the number format, such as 000-00-0000 for a social security number code, or 00000 for a five-digit postal code.

   Tip: You can also click Special, and then select Zip Code, Zip Code + 4, Phone number, or Social Security Number.

   Find more information about custom codes, see Create or delete a custom number format.

   Note: This will not restore leading zeros that were removed prior to formatting. It will only affect numbers that are entered after the format is applied.

Use the TEXT function to apply a format

You can use an empty column next to your data and use the TEXT function to convert it to the format you want.

Number code	Example (In cell A1)	TEXT function and new format
Social security	012345678	=TEXT(A1,"000-00-0000") 012-34-5678
Phone	0012345556789	=TEXT(A1,"00-0-000-000-0000") 00-1-234-555-6789
Postal code	00123	=TEXT(A1,"00000") 00123

Credit card numbers are rounded down

Excel has a maximum precision of 15 significant digits, which means that for any number containing 16 or more digits, such as a credit card number, any numbers past the 15th digit are rounded down to zero. In the case of number codes that are 16 digits or larger, you must use a text format. To do this, you can do one of two things:

• Format the column as Text

  Select your data range and press Ctrl+1 to launch the Format > Cells dialog. On the Number tab, click Text.

  Note: This will not change numbers that have already been entered. It will only affect numbers that are entered after the format is applied.

• Use the apostrophe character

  You can type an apostrophe (') in front of the number, and Excel will treat it as text.

Top of Page

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.