During doing work in a large worksheet, you need to input tin number, pin code or phone numbers with leading zeros, you will find that Microsoft Excel by default automatically remove all leading zeros. This article helps you how to quickly add or insert leading zeros to text or numbers in Excel.
How to add or insert leading zeros to text or numbers in MicroSoft Excel
In this article we have to discuss three quick ways from which you will be able to easily add or insert leading 0 to text or numbers in Microsoft Excel. If you want to add or insert leading zeros to any text or numbers in Microsoft Excel then you just take few simple steps given below.
Method 1: Add or insert leading zeros to text or numbers in MS Excel using ‘ symbol:
If you want to add or insert leading 0 to text or numbers in Microsoft Excel manually then you have to use ‘ (single quote) symbol. If you want to fill tin number of any party like “08231234212” then take following steps given below:
Step 1: Open an existing worksheet or create new worksheet in which you want to add or insert leading 0 to text or numbers in MicroSoft Excel.
Step 2: Press (‘) single quote sign and then type following given tin number 08231234212 and press enter.
Note: If you try to put tin number with using (‘) single quote sign Microsoft Excel automatically remove the leading zero.
Must Read: 2 Ways to Fill blank cells with 0 or any specific value in Excel
Method 2: Add or insert leading zeros to text or numbers in MicroSoft Excel using Text function:
The Text function allows you to easily and quickly converts a value to text in a specific number format. The syntax of Text function is given below:
=TEXT(value, format_text)
Value: It is a number, a formula that evaluates to a number, reference to a cell containing a numeric value.
Format_text: It is a number format of text form from the category box of the number tab in the the Format cell box.
Step 1: Open an existing worksheet or create new worksheet in which you want to add or insert leading 0 to text or numbers in MicroSoft Excel.
Step 2: Enter the following given formula in cell G3 =TEXT(F3,”00000000000″). Press enter to get the result now, you will get a leading zero before the 10 digits. If you want to copy this formula for rest of the cells then drag the fill handle of cell G3 to G7.
Method 3: Add or insert leading zeros to text or numbers in MicroSoft Excel using Concatenate function:
Concatenate is another important text function which is used to join several text strings and convert into a single text string. The syntax of concatenate function is given below:
=CONCATENATE(text1, text2,….)
text1: text1, text2, ….are the text string used to joined each other and provide single text string. You can input up to 255 text strings in the formula.
Step 1: Open an existing worksheet. You can also create new worksheet in which you want to add or insert leading 0 to text or numbers in MicroSoft Excel.
Step 2: Enter the following given formula in cell G3 =CONCATENATE(“0”,F3). Press enter to get the result now, you will get a leading zero before the 10 digit number. If you want to copy this formula for rest of the cells then drag the fill handle of cell G3 to G7.
Must Read: Quick Excel Income tax Calculator for FY 2015-16 AY 2016-17
I hope after reading this article your problem is solved. With the help of these three methods you can easily add or insert leading 0 to text or numbers in MS Excel. If you have any suggestion regarding this article then write us in the comment box. Thanks to all.