Im trying to generate a file to print barcodes from excel. The sequence needs to go
A000001F
A000001B
A000002F
A000002B
A000003F
A000003B
A.....etc
all the way up to A069999F, A069999B, A070000F, A070000B.
Anybody out there that’s proficient in excel able to give me a hand coming up with a solution?
Thanks in advance.
Autofill is not liking the F and B characters on the end, and also the doubled up number means it keeps wanting to repeat the previous sequence, rather than incrementing it every 2 rows.
A000001F
A000001B
A000002F
A000002B
A000003F
A000003B
A000001F
A000001B
A000002F
A000002B
A000003F
A000003B
A000001
A000001
A000002
A000002
A000003
A000003
A000001
A000001
A000002
A000002
A000003
A000003
If you have Microsoft 365,
=LET(
num_format, REPT("0", 6),
count, 7,
TOCOL(
"A" & TEXT(SEQUENCE(count), num_format) &
{"F", "B"}
)
)
=TOCOL(TEXT(SEQUENCE(70000),"A000000")&{"F","B"})
You could use VBA:
Sub GenerateBarcodes()
Dim i As Long
Dim j As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)
For i = 1 To 70000
ws.Cells(2 * i - 1, 1).Value = "A" & Format(i, "000000") & "F"
ws.Cells(2 * i, 1).Value = "A" & Format(i, "000000") & "B"
Next i
End Sub
Or if you have Excel 365
=LET(
seq, SEQUENCE(70000, 1, 1, 1),
num, TEXT(seq, "000000"),
F, "A" & num & "F",
B, "A" & num & "B",
VSTACK(F, B)
)
A more general version for E365 which can be easily used with any number of suffixes:
=LET(
seq, SEQUENCE(70000, 1, 1, 1),
num, TEXT(seq, "000000"),
suffixes, {"F","B","C", any possible number of string or characters},
rows, ROWS(seq),
cols, COLUMNS(suffixes),
total, rows * cols,
result, MAKEARRAY(total, 1, LAMBDA(r,c, "A" & TEXT(INT((r-1)/cols)+1, "000000") & INDEX(suffixes, MOD(r-1, cols)+1))),
result
)
With an older version of Excel, anter “A0000001F” in a cell, enter the formula below under it and drag down as far as needed:
=IF(
RIGHT(B3,1)="F",
LEFT(B3,7)&"B",
"A"&TEXT(MID(B3,2,6)+1,"000000")&"F"
)