May 13 2021 08:51 AM
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
May 13 2021 08:56 AM
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.
May 13 2021 11:53 AM
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
May 13 2021 12:11 PM
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)
May 13 2021 12:36 PM
May 13 2021 12:42 PM
I'm afraid you've lost me.
May 13 2021 01:00 PM
May 13 2021 01:17 PM
Could you attach a sample text file that you want to be converted to Excel?
May 13 2021 01:23 PM
Store |
3 |
4 |
6 |
11 |
19 |
21 |
23 |
25 |
26 |
27 |
28 |
29 |
30 |
33 |
34 |
35 |
37 |
38 |
39 |
43 |
45 |
46 |
51 |
58 |
61 |
63 |
64 |
66 |
68 |
69 |
78 |
80 |
81 |
82 |
83 |
85 |
86 |
87 |
88 |
90 |
91 |
92 |
96 |
99 |
100 |
103 |
104 |
112 |
115 |
117 |
118 |
120 |
121 |
122 |
124 |
127 |
129 |
131 |
132 |
133 |
135 |
136 |
137 |
138 |
139 |
142 |
147 |
149 |
152 |
155 |
157 |
158 |
160 |
161 |
162 |
165 |
166 |
167 |
168 |
171 |
172 |
173 |
174 |
175 |
176 |
177 |
178 |
179 |
181 |
182 |
190 |
191 |
May 13 2021 01:56 PM
SolutionSee the attached workbook - it assumes that you have Office 2019 or Microsoft 365.
May 13 2021 02:57 PM
May 13 2021 03:06 PM
May 13 2021 01:56 PM
SolutionSee the attached workbook - it assumes that you have Office 2019 or Microsoft 365.