Tips & Tricks 30 – Generate an Array of a to z

I have already talked about generating a to z in a sequence in below post.

Now, I want to generate an array {"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v",
"w","x","y","z"} to use in a formula.

To generate Small or Capital letters, do it while generating the sequence itself.

Let's assume that a to z are generated in A1 to A26. Put a in A1 and drag down till A26. If you want to generate a to z in capital letter, put A in A1 and drag down.

In a cell, put following formula and press F9

= TRANSPOSE(A1:A26)

In the formula bar, you will have array generated which you can copy and paste and use in the formula.

Edit – 18-Aug-20

A better solution is copy and pasted from Rick's comments

You can generate that array directly using this formula (no need to put values in any cells)…

=CHAR(96+COLUMN(A1:Z1))

The above generates an array of lower case letters. To generate an array of upper case letters, use this formula…

=CHAR(64+COLUMN(A1:Z1))