5 ways to create an ordered alphanumeric list in Google Sheets

Alphanumeric list in columns rows and cells in Google Sheets v3

For whatever reason, sometimes we just need a list of alphanumeric letters and numbers like this “0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ” to run down a column or across a row, or just all packed into one string of text in a cell in our Google Sheet.

I most commonly use an alphanumeric list to index data or use an alphanumeric string in the random assignment of a value.

In this tutorial, we will cover 4 ways to achieve this. Some approaches have different benefits than others. While two of the approaches even use the new Google Sheets LAMDA function.

If you just want to dive in, copy the formula, and get back into your own project, click on the link to the main numbered headers in the table of contents below. You can copy the formula into your project using the copy symbol in the dropdown menu of the formula bar and selecting the formula you want.

Each formula type can be:

  1. Transposed to run across a row.
  2. Join into a string in a cell.
  3. Set to lowercase.
  4. Extended to include a lowercase option.

The How it Works sections are not mandatory reading but might be helpful if you plan to extend the formula or are simply curious.

Oh, and number 1 is my favorite.

1. ARRAYFORMULA BASE SEQUENCE approach

This approach probably has the smallest visible footprint generating an alphanumeric sequence in Google Sheets. We can convert the standard base 10 (0123456789) to base 36 which makes use of all number characters and all letter characters in the alphabet.

The only downside is that we can’t really add any other character types to the string using the same approach should we want to be more creative.

How it works

Hire a Google Workspace Developer for your Business Needs

base

In this approach, we use the BASE function to generate our alphanumeric list in Google Sheets. This function converts a decimal (1-10) number into a text representation of another base.

But, what is a base?

Base is the number of characters representing a counting system. The most common of which is base 10, a ten-digit counting system also known as the decimal system. After we reach ten in counting in this system, we reuse the numbers to make 11, 12, 13 etc.

Another, base system you might have heard of is the binary system or base 2. This system often represents on/off systems in computing. You know, all those ones and zeroes. 🤖👩‍💻

Likewise, you might recognize a base 16 system when working in your favourite image editor when selecting a hexadecimal colour like sky blue (#87ceeb).

For us, we can use the base 36 system to generate a character between the ten common decimal numbers (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) and the 26 letters of the English alphabet.

In Google Sheets, BASE takes two primary arguments:

  1. The decimal value to convert.
  2. The base.

For us, that means we set our base to 36 and our decimal value to a sequence of 36 numbers starting from zero.

sequence

We can generate a matrix of numbers in Google Sheets by using the SEQUENCE function. This function takes 4 arguments:

  1. Number of rows to generate.
  2. Number of columns to generate.
  3. The starting number.
  4. The step. For example, a step of 2 would generate an array starting at one as, 1, 3, 5, 7, 9 etc.

In our example, we will use the SEQUENCE function to generate a list 36 rows deep of numbers starting from zero. These will be used as reference numbers for our BASE function.

BASE( SEQUENCE(1,36,0,1) ,36 )

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

ARRAYFORMULA

Now, BASE on its own will not be able to create a number for each item in the sequence we generate. It will only apply the base to the first item which will be 0.

To force BASE to work on every item in the array we can use the ARRAYFORMULA function. This function converts single-cell processes into one that can be calculated across an array.

This makes the function an incredibly useful tool and you can see more examples of ARRAYFORMULA in action here.

2. TRANSPOSE SPLIT approach

Not my favourite approach, because it feels like you are doing the bulk of the work in the string. However, it is ideal if you want to build a custom string of characters in a set order.

How it works

In this example, we already have a predefined list of characters in a string separated by numbers. We might be storing these numbers on a separate workings Google Sheet tab.

Our aim is to split these characters and convert them into items across a row or down a column. We can do this with the SPLIT function.

SPLIT takes two primary arguments:

  1. The text we want to split
  2. The delimiter or character/s we want to split by.

For us, our text is separated by a period (.), so that will be our delimiter. The result of the split is a row containing a character from the string of text in each cell.

To have characters run down a column, we then need to use the TRANSPOSE function.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

3. MAP SEQUENCE LAMBDA CHAR approach

I mean, besides the opportunity to play with the new MAP and LAMDA functions, this option is my second favourite. Here we combine two maps to grab the:

  1. Numbers.
  2. Letters.

We use the CHAR function to extract the character from the standard Unicode table. The benefit here is that we can alter the SEQUENCE start and length to capture other characters in the Unicode table.

How it works

char

Besides sounding like a surfer dude’s expression of enthusiasm, the CHAR function allows us to display any character from the many tens of thousands long list of characters in the Unicode chart.

This means we can grab decimal numbers from between CHAR(48) and CHAR(57), lowercase letters from between codes 97 and 122, and uppercase codes between 65 and 90.

map

To iterate through each CHAR set we use the Google Sheets MAP function. This function iterates through each value in a given array and transforms it based on the formulas set inside the LAMBDA function of the MAP.

This leads us to the LAMBDA function. The beauty of this function is that we can create custom functions with a set of named variables. You can even advance this to generate your own named functions in Google Sheets.

Powerful stuff.

The MAP function takes an array set as its first argument. In our example, this is either a sequence of 10 or 26 numbers for the ten digits and alphabet respectively.

Next, we create our LAMBDA function. We first set our custom iterator to ‘cell’ (It can be anything). Then we reference the ‘cell’ variable in our CHAR() function.

This means that each time MAP iterates through the sequence it will set the sequence number to the CHAR() as an argument.

Our number map then would look like this:

MAP( SEQUENCE(10,1,48,1),LAMBDA(cell, CHAR(cell)) )

build the Alphanumeric array

You can see that we have wrapped two maps into curly braces”{}“. In Google Sheets, curly braces allow you to build your own array of cell data. When you separate arrays by a semicolon. “;“, it means that you want to put the next array below the previous one. Likewise, you can use a comma, “,“,  to append an array across a row.

In our first example that generated a column of alphanumeric cell data we used the semicolon separator:

={number_map ; alphabet_map}

4. MAKEARRAY LAMBDA CHAR approach

Another use of the LAMBDA function here, this time in conjunction with Google Sheets MAKEARRAY. With MAKEARRAY we can forego the SEQUENCE function because it does the same thing.

Again, like the MAP approach, we can add other characters from the Unicode chart by setting the start index of CHAR and the length and adding it to the array inside curly braces.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

How it works

The MAKEARRAY function follows the same principles as MAP and SEQUENCE combined. MAKEARRAY allows you to generate a 2d array or matrix based on an iterable formula generated in a LAMBDA function.

MAKEARRAY takes a number of rows and a number of columns as its first two arguments.

We then have the familiar LAMBDA custom function builder.

This time LAMBDA requires us to generate a row and column variable. We set “row_idx” and “col_idx” respectively here.

Finally, we reference our “row_idx” variable inside our CHAR function. It is important to keep in mind that on the first iteration of MAKEARRAY “row_idx” with be equal to one.  So if we want to get, say “A” in position 64 of the Unicode chart then we need to subtract one from the position.

MAKEARRAY( 26,1,LAMBDA(row_idx, col_idx, CHAR(row_idx + 64)) )

Finally, we wrap our number and letter MAKEARRAY set in our array curly braces.

5. ARRAYFORMULA REGEXEXTRACT ADDRESS ROW TRANSPOSE TO_TEXT approach

This is probably my earliest approach to creating an Alphanumeric list in Google Sheets.

Here we are extracting the column and row header ranges and using those to create our alphanumeric list. This means if we ever wanted to continue our lettering to AA, BB, CC etc. we could by extending the COLUMN range.

The downside to this approach is that it is a bit verbose.

How it works

ROW or COLUMN

We can get the row number or column number of a selected cell by using the Google Sheets ROW and COLUMN functions. So ROW(2) would reference row 2 and COLUMN(4) would reference column D and be expressed as 4.

To extract a list of numbers, this is more than enough and we can stop at this stage only to include the TO_TEXT() function to format the number as a text to match the letters.

We also need to subtract one from our result to get the number starting from zero and apply ARRAYFORMULA to iterate through each range.:

=ARRAYFORMULA(TO_TEXT(ROW(A1:A10) -1))

ADDRESS

To extract the column letters we need to go a little further and apply the ADDRESS function.

ADDRESS takes two primary arguments, the:

  1. Row number
  2. Column number

We can set the row to 1 and then set the column to the result of our row. This will return absolute A1 notation cell references like, $A$1, $B$1, $C$1 etc.
=ARRAYFORMULA(ADDRESS(1,COLUMN(A1:Z1))))

REGEXEXTRACT

Next, we can use the REGEXEXTRACT function to extract only the letters from the A1 notation address, converting $A$1 to A.

REGEXEXTRACT takes the text to extract as the first argument and the regular expression used to extract the desired text in the second argument.

We could have set our regular expression to just grab all the capital letters like so, [A-Z]. However, we wanted to futureproof ourselves in case we want to retrieve AA, BB, FFF, GGGGG in the future so added the regular expression plus (+) quantifier that allows you to match one or more occurrences of the preceding element.

Wrapping our ARRAYFORMULA  function around our formula so far, we get a  row or letters in each column. From here we transpose this to run down a column:

=TRANSPOSE( ARRAYFORMULA( REGEXEXTRACT(ADDRESS(1,COLUMN(A1:Z1)),"[A-Z]+") ) )

Finally, we add the capital letter formula to our number formula to get our alphanumeric list.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

Conclusion

That wraps up this tutorial on creating an Alphanumeric list in Google Sheets. So do you think these formulas are useful? How would you apply them to your own projects? I would love to hear in the comments below.

If you have an alternate solution to this problem I would also love to add it to the list. I’ll even give you a special shout-out for being so awesome.

Where to next?

I’m glad you asked.

In the next tutorial, we will make a password generator Named Function in Google Sheets. Make sure you subscribe to get a notification for when this tutorial will come out.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

~Yagi

Leave a Reply