Forum Discussion
Solved
Autofill alphabet
PC/Win10/MS365 Subscriber
I will skip the frustration and disbelief for the moment. I can drag and auto fill a column of numbers with ease. I must be missing something obvious but I have found no technique for this using the default alphabet (in my case obviously US English).
I have seen a technique on a number of help sites suggesting adding a custom series via the Options tab. Seriously!? Come on, I am a retired software engineer. A simple table function would permit using the default language or even if needed, adding custom fill sequences like the Options tab approach suggests, but having to manually generate a sequence of alphabet characters is...!?
So I, with a certain emotion rising set out to get on with my work; but, guess what!? I need more than 26 autofill characters in the sequence (like the column headings so conveniently displayed above the spreadsheet cols I'm using! I could not figure out how to have to columns filled with alpha chars to pair up to sequence "A","B","C",...,"X","Y","Z","AA","AB","AC",...,"AZ","BA","BB","BC",...
Am I nuts or just plain ignorant or ... what!? Actually, the central office of the org I volunteer for has been pushing me to use their standard, central online spreadsheet ... "Google Sheets"! Dare I say that here? (At least "Google" has the neighborly option to export / download in *.xlxs format. I am just piqued enough to even consider that.) I will be most gracious to find out I have somehow overlooked the obvious, simple technique that has eluded me thus far. Thank you!
The attached has a legacy formula as well as the dynamic array formula
= MID(ADDRESS(1, ROW()),2,1+(ROW()>26))I could also include a Lambda function version but that is only useful with the beta release versions of Excel. I am not sure whether APL supported functional programming style; FORTRAN IV certainly didn't!
I approach the problem from the other end. Why should one ever want a labelling sequence which forms neither a numeric sequence (good) nor alphabetic sequence (poor) but, instead, provides an utterly idiosyncratic sequence of letters such as that used for column referencing (appalling)? Answers on a postcard?
[By idiosyncratic, I mean that not many dictionaries list AA after Z]
Returning to your question, you could use a formula instead of a manually filled range.
= LET( ref, ADDRESS(1, SEQUENCE(n)), chars, LEN(ref)-3, MID(ref, 2, chars))n - 26*27 + 1
generates the sequence from A to AAA.
Note: 1) This is an Excel 365 formula but, for me, it is only 365 that allows me to turn my back upon the pile of junk that is tradition spreadsheet working practice.
2) That said, I deeply admire the problem solving skill of some practitioners.
- Hi Peter,
Thank you for the response, point well taken. I admit that your formula is a bit above my pay grade. Might you give a quick example? Thanks, +/Chuck ("+/" is from my days doing APL... showing my age a bit ;-))The attached has a legacy formula as well as the dynamic array formula
= MID(ADDRESS(1, ROW()),2,1+(ROW()>26))I could also include a Lambda function version but that is only useful with the beta release versions of Excel. I am not sure whether APL supported functional programming style; FORTRAN IV certainly didn't!
