Method 1 – Convert Numbers to the Text Format to Add Leading Zeroes in Excel
Steps:
- Insert an Apostrophe (‘) before starting a number to add 0 at the front. The Apostrophe will not show in the cell but will throw errors if you try to use the cell for calculations.
- Put an Apostrophe in all cells and add the necessary zeroes manually.
NOTES:
- Adding an Apostrophe at the front will convert the number to Text format. So, you can’t use them for any calculation,
- In the top left corner of the cells, you will see a green tiny arrow symbol. After clicking on the arrow, you’ll get a message “Number stored as text”. To continue as-is, click on the option “Ignore Error”
- Using this method will not automate the other cells.
Alternative Steps:
- Select the cells of the column.
- Go to the Number tab on the top ribbon.
- Open the drop-down menu.
- Select the Text option.
- The cells will be converted into the Text format.
- You can add necessary zeroes in front of numbers.
Read More: How to Add Leading Zeros in Excel Text Format
Method 2 – Use Custom Formatting
Steps:
- Select the cells containing the numbers.
- Go to the Home tab ribbon and click the arrow in the bottom-right corner of the Number group.
- You’ll get a window named Format Cells.
- Stay in the Number tab and go to the Custom option.
- In the Type box, write 0000 to make a 4-digit number.
- Press the OK button.
- The Output column has been converted to four-digit numbers with leading zeroes.
NOTES:
- The numbers will remain in the Number format.
- If you copy and paste them as values, they’ll lose the formatting.
- In the formula bar, you will see the original values.
- You can control how many digits you need with the number of zeroes in the format.
- Numbers higher than the specified number of digits won’t be affected.
Read More: How to Add Leading Zeros to Make 10 Digits in Excel
Method 3 – Use the Ampersand (&) Symbol
Steps:
- Insert the following formula in cell C5:
="00"&B5- This will add two zeroes in front of the number from cell B5.
- Copy the formula and paste it into other cells or use the Fill handle.
NOTES:
- This converts the values to text.
- The formula adds two leading zeroes to all values regardless of its existing length, so it won’t make them the same length.
Method 4 – Utilize Excel Functions to Get Zeroes in Front of Numbers
Case 4.1 – Using the REPT Function
Syntax:
=REPT (text, number_times)
Text: The text or character that will be repeated
Number_times: The number of times to repeat the text.
Steps:
- Input this formula in cell C5.
=REPT(0,2)&B5
- Copy and paste the formula into the other cells.
Let’s modify the formula to make all results at least six characters long.
- Insert this formula into cell C5.
=IF((LEN(B5)<6),REPT(0,6-LEN(B5))&B5,B5)
Formula Explanation:
- LEN(B5): Calculates the number of characters in cell B5
- LEN(B5)<6: The condition that if the number of characters in cell B5 is less than 6
- 6-LEN(B5): Returns the value that is needed to make the number get 6 digits.
- REPT(0,6-LEN(B5))&B5: Adds the required zeroes in front with cell B5
- =IF((LEN(B5)<6),REPT(0,6-LEN(B5))&B5,B5): Wraps the formula in an IF function to ignore numbers that are already six digits long or longer.
- Copy and paste the formula to other cells using the Fill Handle icon or shortcuts.
NOTES:
- As the cells remain in Number format, you can use them for any calculations.
Case 4.2 – Using the TEXT Function
Syntax
=Text(Value, format_text)
Value: Value that you will convert to text
Format_text: Format that you want to use.
Steps:
- Insert this formula into cell C5
=TEXT(B5,"0000")- This will convert the value of cell B5 to Text format and will add the necessary zeroes to make it 4 digits.
- Copy and paste the formula to other cells using the Fill Handle icon or shortcuts Ctrl + C and Ctrl + V.
Case 4.3 – Using the CONCAT or CONCATENATE Function
Steps:
- Insert the following formula in the cell C5:
=CONCAT("00",B5)
- Use the Fill Handle icon to copy and paste the formula to the other cells.
NOTE:
This will just add two zeroes to all cells.
Case 4.4 – Using the RIGHT Function
Syntax
=RIGHT (text, [num_chars])
Text: Value that is being extracted from.
Num_chars: Number of characters that will be extracted from the right. Omitting this returns the last character only.
Steps:
- Insert the following formula in cell C5.
=RIGHT("00"&B5,4)
- Use the Fill Handle icon to copy and paste the formula to the other cells.
This formula will trim numbers that have more than four digits and will return a three-digit number for a single-digit input.
Case 4.5 – Using the BASE Function
Syntax:
=BASE (number, radix, [min_length])
Number: Number that will be converted. It must be an integer value and >= 0.
Radix: Base radix to which the number will convert. Must be an integer >=2 or <=36.
Min_length: The minimum length of the string
Steps:
- Insert the following formula in cell C5.
=BASE(B5,10,4)
- Use the Fill Handle icon to copy and paste the formula to the other cells.
Read More: How to Add Leading Zeros in Excel
Method 5 – Use the Power Query Tool
- Go to the Data tab, select Get Data, and choose Launch Power Query Editor.
- You’ll get a window named Power Query Editor.
- Go to the Home tab, click on the New Source drop-down menu, and select Excel Workbook.
- Select a workbook and extract data from it.
- Select the option Enter Data and input data manually.
- Make a column by pasting the data from the workbook or inputting data manually.
- Press OK.
- Go to the Add Column tab and choose the Custom Column option.
- You’ll get a Custom Column window.
- Name the column.
- Insert the formula given below:
=Text.PadStart([Number],4,"0")
- Press OK.
- You will get a column with the name you chose where the cells have at least four digits.
- Select Close & Load.
- You’ll get a table in a new sheet with the results.
Things to Remember
- Using an Apostrophe at the front will convert the number to Text format and you have to add zeroes in front manually.
- A Custom Format is the most useful option to add zeroes and make numbers of the same digits.
- Formatting numbers to Text will allow you to add zeroes in front but hampers calculations.
- The REPT function will add the specified number of zeroes no matter the number of digits. Use the REPT, LEN, and IF functions together for better results.
- The TEXT function effectively applies a Custom Format.
- The CONCAT function will also add the given amount of zeroes no matter the number of digits.
- The RIGHT and BASE functions make the numbers have the same number of digits.
- Power Query will help to extract data and format them from external sources.
Download the Practice Workbook
Related Articles
<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
