This method won’t work if you need these numbers for use in formulas. Go to method 4, Creating Custom Formats, to keep the data as a number type. Check out our guide on typing formulas in Excel to learn more about formulas!
Excel automatically and permanently removes leading zeros when you import data into a workbook. The Get & Transform utility allows you to designate certain columns as text during the data importing process. This keeps the leading zeros intact. Alternatively, you can open an existing workbook if you have one that you want the data to be imported to. Refer to our article on downloading Excel if you need to get Excel on a new device.
If you don’t see this option, you may have a version of excel that is arranged differently. Follow the method below (Importing Data as Text) instead.
If you don’t see this option, you may have a version of excel that is arranged differently. Follow the method below (Importing Data as Text) instead.
Go to the Home tab. Locate the Transform section. Click Data Type. Choose Text. This will open a confirmation “Change Column Type” window. Click Replace Current in the Change Column Type window. Repeat this process for any columns with leading zeros that you want to keep. You can do this for several columns at the same time by selecting multiple column headers with Ctrl + Left-Click.
Now you’re ready to make some tables or concatenate some text!
This method does allow you to import some columns as text and some columns as numbers. Only the text columns will keep leading and trailing zeros.
On old versions of Excel without a ribbon menu, instead click Format → Cells → Number tab. [5] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
If your data represents calendar dates, phone numbers, or other common data types, browse the list of default options. You may be able to use one of them rather than creating your own format.
Numbers to the left of the decimal point are always displayed. Even though there are only three digits in the format code 000, the number 3750 will still display as 3750.
13. 1 becomes 013. 100 95001 becomes 95001. 000 5. 0057 becomes 5. 006
5. 8 becomes 05. 8 2. 71 becomes 02. 71 15. 0 becomes 15 38. 315 becomes 38. 32
If your data includes digits after a decimal point, prevent rounding by using the custom format “00”#.
instead, using as many # or 0 characters after the decimal point as necessary.
?. ??? rounds to the nearest thousandth with no trailing zeroes, and aligns the decimal points. 000. ? adds leading zeros up to the thousands place, rounds to the nearest tenth with no trailing zeros, and aligns the decimal points. ?. 00 rounds to the nearest hundredth with trailing zeros and aligns the decimal points.
