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

Mechanicaleng blog

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
    • powerpoint
    • excel tips
  • 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.

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

Related posts:

change cell background color automatically in excelchange cell background color in excel by equal formulas Random number from fixed set of optionsCreate random number in excel Create border in excel by border tool2 ways to create border in excel How to count blank cells in excelHow to count and color blank cells in excel

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

  • 10 things to succeed in mechanical engineering
  • what is tapered roller bearings structure
  • What is a flange connection
  • What is a Stainless Steel Flexible Joint
  • Gearbox – What You Need To Know
  • Gearbox Lubrication | 2 types lubricant
  • stainless steel | 4 types and identify
  • Plastic milling – simple but not simple
  • what is spur gear | introduction
  • Mechanical engineering skills from training process

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