Recommended Free Tools
Google Sheets automatically treats entries like 00123 as numbers, so it strips the leading zeros and stores the value as 123. That behavior is useful for calculations, but it can break data such as ZIP codes, employee IDs, invoice numbers, SKU codes, product codes, and account numbers where every digit matters.
Stopping this usually means choosing the right approach before or after the data enters the sheet. You can format cells as plain text, type an apostrophe before a value, apply a custom number format, adjust CSV import settings, or use formulas to rebuild fixed-length codes with zeros restored.
As an Amazon Associate I earn from qualifying purchases.
Why Google Sheets Removes Leading Zeros
Google Sheets removes leading zeros because it tries to interpret what you type as the most useful data type. When you enter 00123 into a cell with the default automatic formatting, Sheets treats it as a number. Since the numeric value of 00123 is the same as 123, the leading zeros are considered unnecessary and are removed from the displayed value.
This behavior is normal for calculations, but it creates problems for data that looks numeric while actually functioning as text. ZIP codes, employee IDs, account numbers, invoice codes, product SKUs, tracking numbers, and school IDs may contain leading zeros that are part of the identifier. In those cases, 00745 and 745 are not interchangeable, even though Sheets sees them as the same number unless told otherwise.
#1 Best Overall
- FULL HD IPS DISPLAY - Enjoy vibrant, crystal-clear images with 178-degree wide-viewing angles
- AMD RYZEN 3 30 PROCESSOR - Everyday performance you can count on; Multitask, stream, game casually, and edit photos smoothly with responsive power and vibrant HDR visuals
- ENJOY UP TO 14 HOURS AND 15 MINUTES OF BATTERY LIFE - HP Fast Charge restores battery from 0 to 50% in approximately 45 minutes
- AMD RADEON 610M GRAPHICS - Experience smooth entertainment; Built for streaming and multitasking, enjoy realistic visuals and efficient performance for work and play
- STORAGE AND MEMORY - 512 GB PCIe NVMe M.2 SSD offers fast speed and efficient storage; and 8 GB LPDDR5 RAM memory boosts performance with higher bandwidth
The distinction comes down to whether the value should be stored as a number or as text. Numbers are meant for math: totals, averages, sorting by value, charts, and calculations. Text values are meant to preserve characters exactly as entered. If a code must keep every digit in a fixed position, including zeros at the beginning, it should usually be handled as text rather than as a number.
Custom number formatting can make this a little confusing. For example, you can format a cell so the number 123 appears as 00123. In that case, the underlying value is still 123; Sheets is only changing how it is displayed. This is useful for fixed-length numeric codes, but it is not the same as storing the actual value as text. If you export the sheet, copy the value elsewhere, or use it in certain formulas, the zeros may not behave the way you expect.
The same automatic conversion can happen when pasting data or importing files. A CSV file might contain ZIP codes such as 02108, but if Google Sheets detects that column as numeric during import, it may convert the value to 2108. Once that conversion happens, Sheets no longer knows that a zero was originally present unless you restore it using formatting, formulas, or the original source file.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Understanding this difference helps you choose the right fix. Use plain text when the zeros are part of the actual data and must be preserved exactly. Use custom number formatting when the value is truly numeric but should appear with a consistent number of digits, such as a five-digit internal code generated from numbers.
Format Cells as Plain Text Before Entering Data
The safest way to stop Google Sheets from removing leading zeros is to format the cells as Plain text before you type or paste the values. This tells Sheets to treat entries such as 00501, 000123, or AB-00742 as text strings instead of numbers. Since the values are no longer interpreted as numeric data, Sheets will not trim the zeros at the beginning.
To do this, select the cells, columns, or range where the data will go. Then choose Format > Number > Plain text. After that, enter your ZIP codes, customer IDs, employee numbers, SKU values, or other codes normally. For example, if column A will contain five-digit ZIP codes, click the column letter A, apply Plain text, and then begin entering values like 02108 or 00544.
How to apply Plain text before entry
- Select the target cells, a full column, or multiple columns.
- Open the Format menu.
- Choose Number.
- Select Plain text.
- Type or paste the values into the formatted range.
This method works especially well when the data is not meant for calculation. ZIP codes, account numbers, invoice references, tracking numbers, serial numbers, student IDs, and product codes may contain digits, but they usually function as labels. Treating them as text preserves the exact characters you enter, including leading zeros, hyphens, and mixed letters.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →| Data type | Use Plain text? | Example |
|---|---|---|
| ZIP code | Yes | 00501 |
| Employee ID | Yes | 000847 |
| Product SKU | Yes | SKU-0042 |
| Quantity or price | No | 42 or 19.99 |
Plain text should be applied before the data is entered. If you type 00045 first and Sheets changes it to 45, switching the cell to Plain text afterward will not bring the missing zeros back. In that case, you must re-enter the value, paste it again after formatting the range, or rebuild it with a formula such as TEXT or RIGHT. For new sheets, it is often easiest to format entire columns in advance so anyone adding data later does not accidentally lose the zeros.
Use Plain text when the stored value must remain exactly as entered. Use custom number formatting instead when you want the value to remain numeric for calculations but display with leading zeros. For example, an internal code like 000123 is usually better as Plain text, while a sequence number that needs sorting or arithmetic may be better handled with a numeric value plus a custom format.
Add an Apostrophe Before the Number
One of the quickest ways to stop Google Sheets from deleting leading zeros is to type an apostrophe before the value. For example, enter ‘00123 instead of 00123. Google Sheets treats the entry as text, so the leading zeros stay visible in the cell. The apostrophe is not displayed in the cell after you press Enter, but it remains visible in the formula bar when the cell is selected.
Rank #2
- Intel Celeron N4120: 4 Cores & Threads, 1.1GHz Base Clock, Up to 2.6GHz Boost Clock, 4MB Cache, Intel UHD Graphics 600. The perfect combination of performance, power consumption, and value helps your device handle multitasking smoothly and reliably with four processing cores to divide up the work.
This method is useful when you only need to enter a few values manually, such as ZIP codes, employee IDs, invoice numbers, SKU codes, or account numbers. It works immediately and does not require changing the format of an entire column. For example, if you type ‘00501, the cell will show 00501 instead of converting it to 501.
How to enter a value with a leading apostrophe
- Click the cell where you want to enter the value.
- Type an apostrophe followed by the number, such as ‘00047.
- Press Enter.
- Confirm that the cell displays the full value, including the leading zeros.
The apostrophe method is best for values that are identifiers rather than numbers used in calculations. ZIP codes, product codes, tracking numbers, student IDs, and department codes often look numeric, but they usually should not be treated as numbers. You typically do not add, subtract, average, or mully these values, so storing them as text is appropriate.
There are a few tradeoffs. Because the value is stored as text, formulas that expect a true number may not work the way you expect. For example, arithmetic operations may require conversion first, and sorting can behave like text sorting rather than numeric sorting. A column containing both real numbers and apostrophe-prefixed text values can also become inconsistent, which may cause issues with filters, lookups, or imported data.
| Entered value | Displayed result | Stored as |
|---|---|---|
| ‘00789 | 00789 | Text |
| 00789 | 789 | Number |
| ‘02108 | 02108 | Text |
If you are filling a full column with codes, formatting the column as plain text before entering data is usually cleaner. The apostrophe method is better for quick one-off entries, small corrections, or situations where you cannot change the format of the sheet. If the values must keep a fixed length but still behave like numbers in calculations, custom number formatting may be a better option than using an apostrophe.
Use Custom Number Formatting to Display Leading Zeros
Custom number formatting is a good choice when the value in Google Sheets should remain a number, but you want it to appear with a fixed number of digits. For example, if you enter 42 and apply a five-digit custom format, Sheets can display it as 00042 while still treating the underlying value as the number 42. This is useful for numeric codes that always have the same length, such as store numbers, invoice sequences, batch numbers, or internal reference numbers.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsTo apply this manually, select the cells or column you want to format, then go to Format > Number > Custom number format. In the custom format box, type a pattern using zeros, such as 00000, then click Apply. Each zero in the pattern represents a required digit. If the number has fewer digits than the pattern, Google Sheets fills the missing positions with leading zeros. A value of 7 becomes 00007, 123 becomes 00123, and 12345 stays 12345.
Common custom formats
| Format | Entered value | Displayed result | Common use |
|---|---|---|---|
| 00000 | 902 | 00902 | Five-digit ZIP codes or numeric IDs |
| 000000 | 1845 | 001845 | Six-digit employee or product numbers |
| 000-000 | 1234 | 001-234 | Fixed-length codes with separators |
| “ID-“00000 | 89 | ID-00089 | IDs with a constant prefix |
Use custom number formatting when calculations, sorting by numeric value, or numeric comparisons still matter. Since the stored value remains numeric, formulas such as SUM, MAX, and numeric filters continue to work normally. It also keeps the sheet cleaner when the zeros are only for presentation. For instance, a report can show branch number 00014 while formulas still read it as 14.
There is one limitation: custom formatting changes only how the value is displayed, not the actual stored value. If you copy the cell into some external systems, export to CSV, or reference it in a formula that reads the raw number, the leading zeros may not be included. For data where the zeros are part of the real identifier, such as ZIP codes, SKU strings, account numbers, or codes that may contain letters, formatting as Plain text is usually safer. Custom number formatting is best when every entry is numeric, every entry should have the same displayed length, and the leading zeros are only a visual requirement inside the spreadsheet.
Preserve Leading Zeros When Importing CSV Files
CSV imports are one of the most common places leading zeros disappear in Google Sheets. A CSV file does not store rich spreadsheet formatting; it is plain text separated by commas. When Google Sheets opens or imports it, Sheets guesses the data type for each column. If a column looks numeric, values such as 00501, 000123, or 0123456789 may be converted to numbers, which removes the zeros at the beginning.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
The safest method is to create a blank sheet, format the target columns as Plain text, and then import the CSV into that prepared sheet. For example, if column A contains ZIP codes and column B contains product IDs, select those columns first, go to Format > Number > Plain text, and then use File > Import. Choose your CSV file and select an import option that inserts or replaces data in the current sheet. This gives Sheets less opportunity to reinterpret those columns as numbers.
Rank #3
- Stunning 15.6" FHD IPS Display: Experience crisp 1920x1080 resolution on this 15.6 inch laptop with an IPS panel that delivers wide viewing angles and vivid colors. The narrow-bezel design maximizes screen real estate for comfortable viewing on this Win 11 laptop, whether you're studying or working.
- Celeron J4105 Processor & 256GB SSD: Powered by a reliable Celeron J4105 processor paired with 12GB DDR4 memory and a fast 256GB M.2 SSD. This laptop computer supports SSD expansion up to 2TB and TF card expansion up to 1TB, so your storage grows with your needs. Delivers smooth multitasking for daily productivity.
- AI-Powered Win 11 Laptop: Built-in AI features enhance your productivity with smart assistance for writing, summarizing, and task management. Pre-installed with Win 11 and includes Office 365 subscription. This student laptop is backed by 1-year warranty and 24/7 customer support.
- All-Day 7000mAh Battery & 180° Hinge: The high-capacity 7000mAh battery keeps this laptop powered through long classes or meetings. The 180-degree lay-flat hinge lets you share your screen effortlessly during presentations. This durable laptop computer adapts to your dynamic workflow.
- Versatile Connectivity Hub: Equipped with USB 3.2, Type-C, Mini HDMI, and 3.5mm audio jack to connect all your peripherals. Stay online anywhere with high-speed 5G WiFi and Bluetooth 4.2. This college laptop keeps you connected at home, in the library, or on the go.
If you use File > Open or upload the CSV directly from Drive, Google Sheets may auto-convert values before you can apply formatting. For sensitive fields such as account numbers, employee IDs, SKU codes, tracking numbers, ZIP codes, and UPC-like values, avoid opening the CSV directly when possible. Import into a sheet where the destination columns have already been set to plain text, or use a controlled import method with formulas.
Use a formula-based CSV import for more control
When the CSV is available by URL, IMPORTDATA can bring the rows into Google Sheets, but it may still treat numeric-looking fields as numbers depending on how the data is parsed. A safer workflow is to import the data into a staging tab, then convert the relevant columns in another tab using text formulas. For instance, if imported ZIP codes are in column A and should always be five characters, use:
=ARRAYFORMULA(IF(A2:A=””,””,TEXT(A2:A,”00000″)))
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallFor nine-digit ZIP+4 base values, product numbers, or fixed-length IDs, adjust the zero pattern to match the required length. A 10-character ID can be restored with:
=ARRAYFORMULA(IF(B2:B=””,””,TEXT(B2:B,”0000000000″)))
This approach is useful when the original CSV values are supposed to be fixed-width numeric codes. It is less reliable if codes can contain letters, hyphens, spaces, or variable lengths, because once Sheets has converted a value like 000A12 incorrectly or altered a long number, the original text may not be recoverable from the converted result.
Prepare the CSV before importing
If you control the CSV export, you can reduce problems before the file reaches Google Sheets. Export code-like fields as text rather than numbers from the source system. Some tools allow fields to be wrapped in quotes, such as “00501”, but quotes alone do not always stop Sheets from interpreting the value as a number. A more dependable option is to ensure the receiving sheet columns are plain text before import.
- Use plain text when the leading zeros are part of the actual value, such as ZIP codes, customer IDs, invoice codes, and product SKUs.
- Use custom number formatting only when the underlying value can safely remain numeric and you only need a fixed-width display.
- Use formulas when data has already been imported and the correct length is known, such as five-digit ZIP codes or eight-digit employee numbers.
- Keep the original CSV unchanged as a backup, especially before cleaning or converting large imports.
For recurring imports, create a template Google Sheet with the correct columns already formatted as plain text. Import each new CSV into a copy of that template, then validate a few rows with known leading zeros before sharing, sorting, or using the data in lookups.
Restore Leading Zeros with Formulas
If Google Sheets has already stripped the leading zeros from a column, formulas can rebuild the values in a consistent text format. This is useful when you receive a sheet where ZIP codes, employee IDs, invoice numbers, or product codes have been converted to numbers. The original zeros are gone as stored characters, but you can add them back when you know the required final length.
Pad numbers to a fixed length with TEXT
The most common method is the TEXT function. It converts a number into text using a pattern you define. For example, if cell A2 contains 123 and the correct code should be five digits, use:
Rank #4
- Efficient Performance for Everyday Computing: Powered by Intel N150 processor with up to 3.6 GHz Intel Turbo Boost Technology, 6 MB L3 cache, 4 cores, and 4 threads, this HP laptop delivers responsive performance for web browsing, streaming, document editing, and multitasking. Paired with 4GB LPDDR5 RAM and 128GB UFS storage, it handles daily tasks smoothly. Includes 1-year Microsoft 365 Personal subscription for Word, Excel, PowerPoint, and cloud storage to maximize your productivity.
- 14-Inch HD Micro-Edge Display:Enjoy clear visuals on the 14-inch HD (1366 x 768) anti-glare screen with 250-nit brightness and 62.5% sRGB coverage. The micro-edge bezel delivers a 79% screen-to-body ratio in a compact design. An HP True Vision 720p HD camera with noise reduction and dual-array microphones supports clear video calls, remote work, and online learning.
- Modern Connectivity and Wireless Technology: Stay connected with Wi-Fi 6 (2x2) for faster wireless speeds and Bluetooth 5.4 for seamless pairing with accessories. Versatile port selection includes 1 USB Type-C 10Gbps with DisplayPort 1.2 for external displays, 2 USB Type-A 5Gbps ports for peripherals, 1 HDMI 1.4b port, 1 headphone/microphone combo jack, and 1 multi-format SD media card reader. Connect monitors, transfer files quickly, and expand your workspace with ease.
- All-Day Battery Life and Portable Design: Enjoy up to 11 hours of video playback, 7.5 hours of mixed usage, or 7.5 hours of wireless streaming on a single charge, perfect for students and professionals on the go. Weighing just 3.24 lb and measuring 12.76" x 8.86" x 0.71", this lightweight laptop fits easily in backpacks and bags. The stylish willow green top cover with matte finish and natural silver keyboard deck with vertical brushing pattern offer a modern, professional look.
- AI-Enhanced Productivity: Access Microsoft Copilot instantly with the dedicated Copilot key for faster assistance. AI Noise Reduction filters background sounds and improves voice clarity during calls. Dual speakers provide clear audio, while the full-size natural silver keyboard and HP Imagepad support comfortable typing and navigation.
=TEXT(A2,"00000")
This returns 00123. Each zero in the format pattern represents one required digit. For a six-digit ID, use "000000". For a nine-digit product code, use "000000000". The result is text, so Google Sheets will preserve the zeros in the formula output.
| Original value | Formula | Result |
|---|---|---|
| 42 | =TEXT(A2,"00000") |
00042 |
| 7812 | =TEXT(A2,"000000") |
007812 |
| 305991 | =TEXT(A2,"000000000") |
000305991 |
Fill an entire column with ARRAYFORMULA
For a full column, you can avoid copying the formula row by row. If your stripped values start in A2 and should all be five characters long, enter this in B2:
=ARRAYFORMULA(IF(A2:A="","",TEXT(A2:A,"00000")))
The IF part keeps blank rows blank, and ARRAYFORMULA applies the conversion down the column. This is especially helpful after importing a CSV file where a whole field of codes has been converted to numbers.
Use RIGHT and REPT for flexible padding
Another formula pattern uses RIGHT with REPT. This approach is useful when you want to pad a value to a target length without relying on a number format pattern:
=RIGHT(REPT("0",5)&A2,5)
If A2 contains 87, the formula returns 00087. The REPT("0",5) portion creates five zeros, the value from A2 is added to the end, and RIGHT keeps the last five characters. To make eight-character codes, change both 5s to 8.
Convert formulas to fixed text values
After restoring zeros with formulas, you may want to replace the original column or export the corrected results. Select the formula output column, copy it, then use Edit > Paste special > Values only. This keeps the displayed codes as static text instead of formulas. You can then rename the column, delete the old stripped data, or use the cleaned values for mail merges, uploads, labels, and imports into other systems.
Use formulas when the data has already been changed and the correct length is known. Use plain text formatting before entry or import when you need to preserve the exact characters from the start. For fixed-length identifiers, TEXT is usually the fastest repair; for mixed workflows or reusable templates, ARRAYFORMULA can keep an entire column corrected automatically.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Best Practices for ZIP Codes, IDs, and Codes
Leading zeros usually matter most in fields that are identifiers rather than quantities. ZIP codes, employee IDs, invoice numbers, account numbers, SKU values, UPCs, and product codes should generally be treated as text in Google Sheets. You are not adding, averaging, or mullying these values; you are preserving an exact label. If a value must remain exactly as entered, especially when it may begin with 0, format the column as Plain text before typing, pasting, or importing the data.
For ZIP codes, plain text is usually the safest choice. A U.S. ZIP code such as 00501 must keep all five digits, and ZIP+4 values such as 00501-1234 are not numbers at all because they contain a hyphen. Custom number formatting can display five digits with a pattern like 00000, but the stored value is still numeric. That may be acceptable for simple five-digit ZIP codes, but it can cause problems if the sheet is later exported, joined with another dataset, or used in a mail merge. For address lists and customer records, use text.
Free tools Windows power users keep installed
One-click scans. No signup required.
Custom number formatting is better when the value is truly numeric but needs a fixed-width display. For example, if order sequence number 42 should appear as 000042, a custom format such as 000000 is convenient because the underlying value remains 42. This allows sorting and calculations to behave like numbers while still showing a padded code. Use this approach only when you are comfortable with the original leading zeros being display formatting rather than stored characters.
Best Value
- 【Expansive Display】The 14 Non-touch display offers clear, and anti-glare coating, perfect for both work and entertainment.
- Designed for mobility with a slim 0.71-inch profile and lightweight, making it easy to carry between home, office
- 【Versatile Connectivity】 connected with multiple ports including USB 3.0 Type-C, and a headphone/mic combo jack, with Wi-Fi and Bluetooth for seamless wireless networking.
- One Year Microsoft 365
Choosing the right method
| Data type | Recommended approach | Example |
|---|---|---|
| ZIP codes and postal codes | Plain text | 00501, 02108, 00501-1234 |
| Employee, student, or customer IDs | Plain text if the ID is assigned; custom format only for generated numeric sequences | 000739, A00452 |
| SKUs and product codes | Plain text | 00045-BLK, 01234 |
| Sequential invoice or ticket numbers | Custom number format or formula padding, depending on export needs | 000001, 000002 |
When sharing a sheet with other people, protect identifier columns from accidental conversion. Set the format first, add a header such as ZIP Code or Customer ID, and consider applying data validation that requires text length or a specific pattern. For example, a ZIP code column can be checked for exactly five characters, while a product code column can be checked for a required prefix. This reduces the chance that someone pastes numeric-looking values and loses zeros without noticing.
If the data will move between systems, test the full workflow. Download a copy as CSV, reopen it, and confirm that values such as 000123 still appear correctly. CSV files do not store Google Sheets formatting, so values preserved only by a custom number format may lose their zeros when opened elsewhere. For durable identifiers, store the zeros as actual text characters; for visual-only padding inside the sheet, custom number formatting is faster and cleaner.
Frequently Asked Questions
How do I stop Google Sheets from removing leading zeros before I type the data?
Select the cells or column first, then go to Format > Number > Plain text before entering the values. Google Sheets will then treat entries like 00123 as text instead of converting them to the number 123. This is the safest option for ZIP codes, employee IDs, account numbers, and product codes that should not be used in calculations.
Free tools Windows power users keep installed
One-click scans. No signup required.
Can I add leading zeros back after Google Sheets already removed them?
Yes, if every value should have the same length, you can use a formula such as =TEXT(A2,”00000″) for 5-digit ZIP codes or =TEXT(A2,”000000″) for 6-digit IDs. This creates a text version with the missing zeros restored. If the original codes had different lengths and that length information is gone, Google Sheets cannot always know how many zeros were originally there.
Should I use plain text or custom number formatting for leading zeros?
Use plain text when the zeros are part of the actual value, such as ZIP codes, SKU numbers, serial numbers, and IDs. Use custom number formatting when you only need the zeros to appear visually while keeping the underlying value as a number, such as displaying 42 as 00042. For codes that may be exported, copied, matched, or imported into another system, plain text is usually the better choice.
How do I keep leading zeros when importing a CSV into Google Sheets?
During import, avoid letting Google Sheets automatically detect and convert the column type if you have that option available. A reliable workaround is to create a blank sheet, format the target columns as Plain text, and then paste or import the CSV data into those columns. You can also edit the CSV so sensitive code fields are treated as text before importing.
Does typing an apostrophe before a number keep the leading zero?
Yes, entering ‘00123 tells Google Sheets to store the value as text, and the apostrophe will not normally display in the cell. This is useful for quick one-off entries where you do not want to format an entire column first. For large datasets, formatting the column as Plain text is faster and less error-prone.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Bottom Line
Google Sheets removes leading zeros because it treats entries like ZIP codes, IDs, and product codes as numbers unless you tell it otherwise. To preserve them, format cells as plain text before entering or importing data, use an apostrophe for quick manual entry, or apply formulas like TEXT when you need consistent fixed-length values.
Use plain text when the zeros are part of the actual data, such as account numbers or SKU codes, and use custom number formatting when you only need the zeros to appear visually while keeping numeric values. Before pasting or importing large datasets, set the column type first so your codes stay accurate from the start.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
