• Skip to main content
  • Skip to secondary menu
  • Skip to primary sidebar

Mechanicalengblog

siemens nx tutorial & injection molding technology

  • Plastic molding
  • Siemens nx
    • nx modeling
    • nx drafting
    • nx sketching
    • nx assembly
  • Mechanical basic
    • mechanical standard
    • Mechanism video
  • microsoft office
    • microsoft word
  • About me
  • CONTACT ME
Home » microsoft office » excel tips » How to add leading zeros in excel

How to add leading zeros in excel

February 20, 2021 by mechanicaleng blog Leave a Comment

In excel, if you type a number as 01234, it will show 1234 immediately, this is default setting of excel. How to keep it? There are many ways to add leading zeros in excel. If you read this post, I think you will find the best way to do it.

Post contents

  • I. Keep leading zeros in Excel as you type.
  • II. Custom number format.
  • III. Using formula of excel.
    • Share this
    • Related

I. Keep leading zeros in Excel as you type.

I usually type ( ’ ) before font of number that has 0 as leading. Example, ‘01234. I think, it is simple do it.
If you don’t want type “ ‘ “, you can set se cell format is text.
+ Select the cell where you want to input 0
+ Right click and select format cell. The format cell dialog will appear, select text option at number group and click OK.

excel format cell text option

After you type a zero before number, excel will show a small triangle in the top left corner of the cell. To remove that error, click the warning sign and click ignore error.

excel ignore error

II. Custom number format.

+ Select the cell where you want to input 0
+ Right click and select format cell. Select custom option at number group and typing 0000 at type box and click OK. (For this example, I will create 4-digit number)

excel-format cell-Custom number format

III. Using formula of excel.

1. Using CONCATENATE command.
Formula: CONCATENATE(text1, [text2], …)

For this example, formula is CONCATENATE(0,B3) -> Input 1 0 number at the front of B3

Using CONCATENATE command
2. Using REPT and LEN command.
Formula: REPT(0, number of zeros-LEN(cell))&cell
For example, I will create I will create 7-digit number: =REPT(0,7-LEN(B3))&B3
LEN(B3): Count all characters in cell B3
REPT(0,7-LEN(B3)) adds the required number of zeros. To know how many zeros will be added, the number is 7 subtract count all characters in cell B3.

excel-Using REPT and LEN command
3. Use right command.
Formula: RIGHT(“00000” & cell, string_length)
In this formula: “00000”: the maximum number zeros you want to get.
Cell: The cell you want to add zeros.
String_length: how many characters the resulting string should contain.
Example:

excel right command

Share this

  • Twitter
  • Pinterest
  • LinkedIn
  • Facebook
  • Email

Related

Filed Under: excel tips Tagged With: excel tutorial

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • How to take screenshot in word document
  • How to create dotted lines in Word very quickly
  • change cell background color in excel by equal formulas
  • How to set password for a Word document
  • How to insert header and footer in excel?
  • split columns and inserting word art in Word
  • 2 ways to create border in excel
  • Add a caption for a picture in word
  • insert math formulas and copyright logo in Word
  • How to automatically create backup in Word

Copyright © 2021 · Magazine Pro on Genesis Framework · WordPress · Log in

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.