2
1
How can I get Excel to AutoFill a pattern as follows (including the leading zero if needed):
010001
011001
012001
020001
021001
022001
...
990001
991001
992001
I can't get Excel to respect the leading zero or detect this pattern.
Is there a trick to it?
Turgs
Posted 2013-01-21T08:55:21.617
Reputation: 153
2
Assuming that you start in row 1, type in the following formula:
=TEXT((INT((ROW()-1)/3)+1),"00")&MOD(ROW()-1,3)&"001"
Peter Albert
Posted 2013-01-21T08:55:21.617
Reputation: 2 802
2
Or you use 2 helping columns and delete them afterwards.
=A1+B1 to concat your strings and fill down column Ccopy column C using only the values
nixda
Posted 2013-01-21T08:55:21.617
Reputation: 23 233
2
If you need it to look like that for display purposes only then here's one way:
Put 10 in A1 then in A2 to A4 use these 3 formulas
=A1+1
=A2+1
=A3+8
Now select A2:A4 together and drag down as far as required
custom format whole column as 000"001"
barry houdini
Posted 2013-01-21T08:55:21.617
Reputation: 10 434
-2
Put a ' infront of you zero to display the leading zero.
e.g.
'0123 would display as 123 in Excel.
kirsty
Posted 2013-01-21T08:55:21.617
Reputation: 1
What Excel version are you using? -- You need to format that column as text and re-enter those values. There are some tricks like using text in columns to re-enter whole columns. – nixda – 2013-01-21T09:09:51.650
I've tried using Excel 2003 for Windows XP (this is the main version it needs to work on) and on Excel 2011 for Mac. I've tried formatting the cell as text, but then it just fills down, copying cells from above rather than incrementing the pattern. – Turgs – 2013-01-21T09:21:09.147