Search code examples
excelexcel-formula

How to reliably increment a Character+Numeric value in excel

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
Solution

  • If you have Microsoft 365,

    =LET(
    num_format, REPT("0", 6),
    count, 7,
    TOCOL(
        "A" & TEXT(SEQUENCE(count), num_format) &
            {"F", "B"}
    )
)

    Result