Forum Discussion
OS windows 2010 windows 365 excel
See the attached workbook - it assumes that you have Office 2019 or Microsoft 365.
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)
- Hi,
no its not converting, just adding the leading zero to 2 digit character
I'm thinking I would key to this formula, I have 50 cells
paste numbers into column 1 , separate each with comma and then transpose to 10 numbers per row. Not sure how to transpose the rows
=TEXT(A1:A50",000")
