Let Excel

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.

The Excel file also can be made to read only by Read-only Recommended or restrict with modifying password. The Excel spreadsheet is locked with password. If the Excel sheet or cell is locked with protection password, you won't be able to make change to it unless. Microsoft Excel is the industry leading spreadsheet software program, a powerful data visualization and analysis tool. Take your analytics to the next level with Excel.

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.

Let Excellence Be Your Brand

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.

Let Function

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.

Excel Let Function Release

Related articles:

Add leading zero to fixed number length in Excel
This article brings three solutions, formulas an a third-part tool, to add leading zeros to show all selected numbers in a fixed length in Excel.

Add or Pad Leading Zeros To Numbers or text In Excel
This article shows you some tutorials about how to add leading zeros to numbers or text in ranges of cells in Excel.

Format cell so leading zeros don’t disappear in Excel
Change a cell formatting by Format Cell or Number Format drop-down list to remain leading zeros before numbers in Excel.

Excel Let Vs Lambda

Keep or delete leading zeros in Excel
This article introduces four solutions to remain or delete leading zeros from numbers in Excel.

• 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 30-day free trial. 60-day money back guarantee.

• 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!

• To post as a guest, your comment is unpublished.
  perfect! thanks for solving my problem.
  

Excel Let Function Not Working

Microsoft Excel Let Function