Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to start numbers with 0 (zero) in Excel?

when we type 0 at the beginning of a number in cell, the 0 will be erased automatically when we press Enter key or move mouse to other cells. Therefore, are there ways to add leading zeros and start numbers with zeros in Excel? Yes! This article introduces 6 solutions for you.

Start numbers with 0 by adding an apostrophe at the beginning of cells

Start numbers with 0 by Format Cells feature

Start numbers with 0 by formulas


Method 1: Start a number with 0 by manually adding an apostrophe at the beginning

You can manually type an apostrophe and zeros before a number to start the number with 0. See screenshot:

Note: This method will change the number to text format.


Method 2: Start multiple numbers with 0 in bulk by a cool took

If you need to start multiple numbers with zeros, manually typing apostrophe and zeros will be time-consuming and tedious. This method will recommend the Add Text feature of Kutools for Excel to add apostrophe and zeros at the beginning of each cells with several clicks.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 60-day, no credit card required! Get It Now

1. Select the number cells, and click Kutools > Text > Add Text to enable the Add Text feature.

2. In the Add Text dialog, please type ‘000 (you can change the number of zeros as you need) in the Text box, check the Before first character option, and click the Ok button.

Note: This method will change all selected numbers to text format.


Method 3: Start numbers with a fixed number of 0 by formatting cells

If you want to add leading zeros to numbers but remain the number formats, you can apply the Format Cells feature to get it done easily in Excel.

1. Select the numbers you will start with zeros, right click, and select Format Cells from the context menu.

2. In the Format Cells dialog, under Number tab, please click to select Custom in the Category list box, type 0000########## into the Type box, and click the OK button.

Notes:

  • In format code 0000##########, 0000 means to display 4 zeros before every selected number.
  • 0000########## will display every selected number as a whole number. If you need to remain decimal places, you can change the format code to 0000##########.00

Now you will see every selected number starts with 4 zeros at once. See screenshot:


Method 4: Start numbers with 0 and display all number in fixed length by formatting cells

In this section, I will guide you to format numbers to display in a fixed length with leading zeros in Excel. Please do as follows:

1. Select the numbers you will start with zeros, right click, and select Format Cells from the context menu.

2. In the Format Cells dialog, under Number tab, click to select Custom in the Category list box, type the format code 000000000 into the Type box, and click the OK button.

Notes:

  • The format code 000000000 will display every selected number in 9 digits . If original number is less than 9 digits, use zeros as placeholders in the left; if original number is greater than 9 digits, the number displays as before.
  • 000000000 will display every selected number as a whole number. If you need to remain decimal places, you can change the format code to 000000000.00.

Now you will see every selected number displays 9 digits with leading zeros. See screenshot:


Method 5: Start numbers with a fixed number of 0 by formula

You can use a formula to add leading zeros to numbers, and force them to start with a fixed number of zeros easily in Excel.

Select a blank cell, enter below formula, and then drag the AutoFill handle down to copy this formula to other range as you need.

=IF(ISNUMBER(B3)=TRUE,"00"&B3,"")

Note: In above formula, B3 is the first number cell you will add leading zeros for.


Method 6: Start numbers with 0 and display all numbers in fixed length by formula

You can also apply formulas to force a number to display in a fixed length with using zeros as placeholder at the left.

Select a blank cell, enter below formula, and then drag the AutoFill handle down to copy this formula to other range as you need.

=TEXT(B3,"000000000")

Note: In above formula,

  • B3 is the first number cell you will add leading zeros for.
  • 000000000 means to display the number in 9 digits. If the number is less than 9 digits, use zeros as placeholders in the left.

Related articles:


Kutools for Excel - The Best Office Productivity Tool Increase Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 60-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.