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.
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.
- Hi Peter, awesome! Thanks for helping an ole dog learn new tricks. I cut my teeth on Fortran at PSU in the Office of Examination Services in 1968. Was in the first class to receive the B.S. in Comp. Sci. (before that it was a sub-degree in Business or Math programs). Went to Xerox and became System Programmer = midnight shifts ;-). Worked my way up to MBA at UR and gained $M for Xerox from in-sourcing APL to become the 2nd largest user of that platform outside of IBM. APL is an interpretive, algorithmic language. Could write a line of APL code that would take pages of C-code to replicate. Of course, few people could pick up another's program and decipher it. Did wind up doing raw HTML code in it prior to it's demise with the advent of mini-computers and PCs. Was a great career, but I have worked even harder since "retirement" but without pay... 😉 Thanks again, +/Chuck
Hi Chuck, as a former APL developer you might be more tolerant than I am of expressions that need to be 'decoded' as opposed to 'read'. A few years ago (2015) I realised that I profoundly disagreed with the proponents of a financial modelling standard as to what constitutes 'best practice' in writing spreadsheet solutions.
[By 'profoundly disagreed' I mean that, for about 25% of their statements, I would replace the word NEVER by ALWAYS]
If you really want to investigate 'new tricks', I could take you through some of the ideas I am exploring. By way of warning, the starting point is 'forget what you think you know about spreadsheets' and begin from scratch with 365 dynamic arrays. No direct cell references and avoid relative referencing wherever possible.
To go from column codes to column numbers I might write
= LET( n, LEN(string), k, SEQUENCE(n), chr, MID(string, k, 1), code, CODE(chr), v, BITAND(code,63), p, 26^(n-k), SUM(v*p) )even though
= COLUMN(INDIRECT(string&1))would be shorter. The former will accept "XFE" as a string to convert, whereas the latter throws a #REF! error.
