• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

5 Ways To Add Leading Zeros To Numbers In Excel

0 Comments

When entering data such as phone numbers, employee codes, etc., it can be frustrating when Excel automatically deletes leading zeros that you want to keep. How do you add zeros at the beginning of a phone number in Excel? Let’s explore the following ways to add zeros in Excel together on Buffcom.net.

Why Add Zeros in Excel?

Have you ever tried to enter data like a phone number or employee code that starts with one or more zeros, such as 0094536416, into Excel? Excel may automatically remove any leading zeros from any number sequence, no matter how hard you try to enter them.

If you want to keep leading zeros in your data, it can be inconvenient, and you may not know why or how to make Excel keep or add zeros in Excel.

Don’t worry, in this article, you will learn nine different ways to add zeros at the beginning of number sequences in Excel.

How to Keep Zeros in Excel When Entering Numbers

Method 1: Add a Single Quote Mark (‘) at the Beginning of the Number Sequence Before Entering Data.

Example: Instead of entering a phone number as 0123456789, enter ‘0123456789. Excel will keep the leading zero of the phone number without deleting it. This is the quickest and simplest way to add zeros in Excel, and the data you enter will automatically be in Text format.

Method 2: Convert the Cell Format to Text

Select the cell or range of cells that you want to keep leading zeros. In the Home tab, under the Number section, select Text as the cell format instead of the default General format.

Add Leading Zeros To Numbers In Excel 1After converting the cell format to Text, a triangle with an exclamation mark will appear in the top-left corner of the cells. You just need to select all the cells -> Click on the yellow triangle -> Ignore Error, and the triangles will disappear.

Add Leading Zeros To Numbers In Excel 2Excel will treat all data you enter as Text, even if they are numbers. This will cause Excel to keep all leading zeros of your numbers.

Note: The two methods above format the text string, which will not be treated as a number and cannot be used for calculations. After entering the data, if you want to use these text strings for calculations, change the cell format back to Number.

How to Add Zeros at the Beginning of Numbers in Excel with Existing Data

If you have an Excel file with existing data, and you want to add zeros to a batch of number sequences without having to edit each cell, you can apply one of the following methods:

Method 3: Adding Zeros in Excel by Customizing Cell Format with Format Cells

Step 1: Select the cell or range of cells where you want to add zeros to the beginning of the number sequence. Open the Format Cells window using one of the three methods:

  • At the Home tab, click on the drop-down arrow of the Number category.
  • Right-click on the selected cells and choose Format Cells.
  • Press Ctrl + 1.

Step 2: Go to the Number tab, select Custom and add a new custom format as desired.

For example, if you want to add two zeros to the beginning of the number sequence so that the total number of digits is 4, enter the format as 0000. Click OK to apply.

Add Leading Zeros To Numbers In Excel 3After applying the custom format, you will see that the cells have been added with zeros at the beginning.

However, the actual data of those cells is not changed. If you select a cell with formatting, you will see that the original number is still displayed in the formula bar, for example, cell D3 shows 0011 but the formula bar displays 11. This also means that if you copy and paste the data as values to another cell, you will lose the leading zeros.

Method 4: Using the TEXT Function to Add Zeros in Excel

The TEXT function allows you to apply custom formatting to any numerical data in your spreadsheet.

= TEXT (value, format_text)

Where: value is the value or range of cells you want to format, format_text is the formatting to apply (usually in double quotes).

For example, if you want to create a 5-digit employee ID using the serial number column, enter the formula =TEXT(B3,”00000″) in cell D3 to apply to that cell, or =TEXT([@STT],”00000″) to apply to the entire column D.

Add Leading Zeros To Numbers In Excel 4Method 5: Using the RIGHT Function to Add Zeros in Excel

You can concatenate a string of zeros with a number and then remove the extra characters using the RIGHT function. The RIGHT function extracts the last N characters from a text value.

= RIGHT (text, [num_char])

Where: text is the text value you want to extract characters from, num_char is the number of characters to extract from the text. If this argument is not entered, only the first character will be extracted.

For example, = RIGHT(“000000” & B3,6) will concatenate a string of zeros with the first number in cell B3 and then return the last 6 characters with leading zeros.

Conclusion on How to Add Zeros in Excel

During data input and editing, you may find it annoying when Excel automatically removes leading zeros, but as you can see, there are many ways to preserve or add leading zeros in Excel. This article has covered 5 common and simple methods for you to apply.

Also, if you are interested in purchasing licensed office software, contact Buffcom.net for more information.

Rate this post
27
274 Views
Excel's IF Function - Examples and UsagePrevExcel's IF Function - Examples and UsageApril 14, 2023
Quickly Import Data From A Website To ExcelApril 14, 2023Quickly Import Data From A Website To ExcelNext

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Kaspersky Internet Security 2021 1 year 5 devices key Global Kaspersky Internet Security 2021 1 year 5 devices key Global
    Rated 5.00 out of 5
    $45.00
  • Windows Server 2022 Datacenter Key Global Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $7.00
  • Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year
    Rated 5.00 out of 5
    $164.50
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
  • Windows 11 Pro Key Global Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $6.00
Products
  • Kaspersky Small Office Security 20 PCs + 20 Mobiles + 2 Servers 1 Year Kaspersky Small Office Security 20 PCs + 20 Mobiles + 2 Servers 1 Year $284.73
  • Windows Server 2022 Standard Key Global Windows Server 2022 Standard Key Global
    Rated 4.10 out of 5
    $7.00
  • Office 2024 Home and Student Office 2024 Home and Student $88.00
  • Windows 10 Pro N Key Global Windows 10 Pro N Key Global
    Rated 4.83 out of 5
    $10.00
  • Buy Microsoft Office Professional Plus 2019 CD Key Global - 5 PC Buy Microsoft Office Professional Plus 2019 CD Key Global - 5 PC
    Rated 4.95 out of 5
    $32.00
  • Buy Windows 11 Pro CD Key License Buy Windows 11 Pro CD Key License
    Rated 5.00 out of 5
    $6.00
  • Buy Windows Server 2016 Essentials Key Global Buy Windows Server 2016 Essentials Key Global
    Rated 4.75 out of 5
    $10.00
  • 5 Key Windows 10 Pro Key Global - Combo 5 KEY 5 Key Windows 10 Pro Key Global - Combo 5 KEY
    Rated 4.81 out of 5
    $25.00
  • Microsoft Visio Professional 2010 Key 1PC Microsoft Visio Professional 2010 Key 1PC $9.00
  • Project Professional 2013 Microsoft Project Professional 2013 - 1 PC $9.00
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.