[Solved] Cannot autofill a long number

[bench27]

Hi
this has bugged me for 2 hours now, so reaching out

i need to create a csv file (ultimately), but it needs to be correct in an excel file first.

i need the following numbers

00200000000000050001
to
00200000000000055000

these would be in column A
in column B
00200000000000055001
to
00200000000000056000

when i use text, it wont allow me to autofill. when i use custom and add the 002000000000000 as an addition to the sample (last 5 digits) it removes 5 zeroes.

im totally stuck. please help!

 

[shamimarita]

Hello Bench27,

Excel struggles to increment such a large number properly with your standard methods. You can use VBA code to generate a sequence with leading zeros.

We treated these large numbers as strings instead of numeric values to handle them correctly.

Sub Generate_Sequences_with_LeadingZeros()

    Dim i As Long
    Dim startNumA As String
    Dim startNumB As String
    Dim ws As Worksheet
    Dim numA As Variant
    Dim numB As Variant
    
    Set ws = ThisWorkbook.Sheets("Solution") ' Adjust to your sheet name
    
    ' Define the starting numbers as strings
    startNumA = "00200000000000050001"
    startNumB = "00200000000000055001"
    
    ' Generate the sequence in column A
    numA = CLng(Mid(startNumA, 17, 5))
    For i = 1 To 5000
        ws.Cells(i, 1).Value = "'" & Left(startNumA, 16) & Format(numA + (i - 1), "00000")
    Next i
    
    ' Generate the sequence in column B
    numB = CLng(Mid(startNumB, 17, 5))
    For i = 1 To 1000
        ws.Cells(i, 2).Value = "'" & Left(startNumB, 16) & Format(numB + (i - 1), "00000")
    Next i
    
    MsgBox "Sequences generated successfully!"

End Sub

Download the Excel file:

 

[bench27]

you nearly created exactly, just an extra zero in the final numbers. should be 50000-55000 & 55001-60000. sorry if i didnt make that clear. unfortunatly the code is way above my level. we actually managed to do it, by using the format tab. we had to keep adding zeroes to the general (example) until it reached 12, but it did work that way! thanks so much for taking the trouble.

 

[shamimarita]

you nearly created exactly, just an extra zero in the final numbers. should be 50000-55000 & 55001-60000. sorry if i didnt make that clear. unfortunatly the code is way above my level. we actually managed to do it, by using the format tab. we had to keep adding zeroes to the general (example) until it reached 12, but it did work that way! thanks so much for taking the trouble.

Congratulations! Glad to hear that you managed to do it by using the Format Cells options.