Excel Formula: Add Leading zero to fix text length
If there is a list of numbers in a worksheet, for looking much neater, you may want to add leading zeros to force them in a fixed length as below screenshot shown. In this tutorial, it provides a formula which uses the TEXT function to easily handle this job.
Generic formula:
| TEXT(txt,"0_length")) |
Arguments
| Txt: the cell reference or text string you want to fix the length. |
| 0_length: a string of zeros which used to set the length of string you need. Supposing you want to make text length in 6 characters, you can use “000000” in the formula as this TEXT(txt,”000000”) |
Remark
This formula will change the cell formatting to text formatting.
This formula only work for number string, if the string is text formatting, it returns the original data.
How this formula work
To fix the numbers in list of cell B3:B8 to five character, please use below formula:
| =TEXT(B3,"00000") |
Press Enter key and drag fill handle to the cell B8.
Explanation
TEXT function is used to convert numbers to text in a specified formatting.
Sample File
Click to download sample fileRelative Formulas
- First letter lower case
Here introduces the formula to add new text to a certain position of the text string. - Trim Text To N Words
Here introduces the formula to extract n words from left side of a text string. - Add comma between names
This tutorial provides formulas to add comma between first name, middle name and last name. - Add dashes to phone number
To add dashes to phone number, you can use a formula to solve it in Excel.
Relative Functions
- TEXT function
Convert number to text with a specific format. - REPLACE function
Find and replace characters based on given location from text string with a new text. - LEFT function
Extract characters from left side of text. - MID function
Extract part of text from a cell value based on the location and length.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
