Forum Discussion
Autofill alphabet
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!
OMG!!! You are BRILLIANT! I thought I knew all of the ends and out for formulas in MS excel. You have put me to shame. I can't even wrap my brain around how your formula works in "Character Sequence". I have played around with it to see what changes and I can't decode it with my puny little brain. Hats off to you Sir. I'm in awe.
