SOLVED

Autofill alphabet

New Contributor

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!

5 Replies

@cswed 

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 ;-))
best response confirmed by cswed (New Contributor)
Solution

@cswed 

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!

 

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

@cswed 

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.