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.
|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”)|
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:
Press Enter key and drag fill handle to the cell B8.
TEXT function is used to convert numbers to text in a specified formatting.
- 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.
- 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.