Forum Discussion
OS windows 2010 windows 365 excel
I have a column of numbers of 2 and 3 digits, I like to have leading zeros for the 2 digit
45
65
789
Need to add leading a zero to 45 and 65
See the attached workbook - it assumes that you have Office 2019 or Microsoft 365.
11 Replies
If it is for display only, apply the custom number format 000 to the column.
If you want the leading zero to be part of the value, do the following.
Let's say the numbers are in A2:A100.
If necessary, insert a column to the right of column A.
In B2, enter the formula =TEXT(A2,"000")
Fill down to B100.
Optionally, select B2:B100.
Copy the range, then paste as values.
You can then remove column A.
Hello, I really appreciate your answer and that worked. Just not sure how to paste on down the column with the formula.
And what if I wanted to paste 2 digit numbers so it will autofill to 3 digits verses having a predefined text spreadsheet?
thanks
Greg Dupree@hteeter
Drag down the fill handle in the lower right corner of the first cell with the formula to copy the formula to the cells below.
If you want to convert 2 digits automatically to 3 digits, that would require VBA code. Would that be acceptable? (macros won't work in the online, iOS and Android versions of Excel)
