Formula Challenge #6: Split A String Into Characters And Recombine In Random Order

This Formula Challenge originally appeared as Tip #194 of my weekly Google Sheets Tips newsletter, on 7 March 2022.

Congratulations to everyone who took part and well done to the 97 people who submitted a solution!

Special mention to Kieran D., Louise A., Martin H., Jelle G., Karl S., Earl N., Doug S., JP C., Alan B., and others for their ingenious solutions. I’ve shared the best below.

Sign up here so you don’t miss out on future Formula Challenges:


Find all the Formula Challenges archived here.

The Challenge Part I: Split A String Into Characters

Question: can you create a single formula to split a string into characters so that each character is in its own cell?

i.e. can you create a single formula in cell B1 that creates the output shown in this example:

Formula Challenge Split String Into Characters

Continue reading Formula Challenge #6: Split A String Into Characters And Recombine In Random Order

How To Use The RANDARRAY Function In Google Sheets

The RANDARRAY function in Google Sheets generates an array of random numbers between 0 and 1. The size of the array output is determined by the row and column arguments.

Here’s a RANDARRAY formula that generates an array of random numbers between 0 and 1, across 10 rows and 3 columns:


which gives an output:

RANDARRAY Function Example

🔗 The RANDARRAY template is available at the bottom of this article.

Continue reading How To Use The RANDARRAY Function In Google Sheets

Build Numbered Lists With The Amazing SEQUENCE Function

The SEQUENCE function is a useful function in Google Sheets. It’s a powerful way to generate numbered lists.

=SEQUENCE(rows, columns, start, step)

As arguments for the SEQUENCE function, you specify 1) the number of rows, 2) the number of columns, 3) a start value, and 4) a step size.

Arguments 2, 3, and 4 are optional. However, if you want to set them you need to include the previous ones (e.g. if you want to set a step size in argument 4, then you need to set 1, 2, and 3 as well).

Keep this order in mind as you look through the examples below and you’ll soon understand how the function works.

1. Ascending list of numbers



Continue reading Build Numbered Lists With The Amazing SEQUENCE Function

How To Get A Unique List Of Items From A Column With Grouped Words

This post shares my solution to a problem that I faced myself recently.

I needed the unique list of items from a column containing grouped words, separated by commas.

The data was in this format:

Grouped data in Google Sheets

As you can see, it’s a list of my online courses, but many of the rows contain more than one item, separated by commas.

Ultimately, I want to transform the data into this format:

Unique List Of Items

Continue reading How To Get A Unique List Of Items From A Column With Grouped Words

Unpivot In Google Sheets With Formulas (How To Turn Wide Data Into Tall Data)

Unpivot in Google Sheets is a method to turn “wide” tables into “tall” tables, which are more convenient for analysis.

Suppose we have a wide table like this:

Wide Data Table

Wide data like this is good for the Google Sheets chart tool but it’s not ideal for creating pivot tables or doing analysis. The main reason is that data is captured in the column headings, which prevents you using it in pivot tables for analyis.

So we want to transform this data — unpivot it — into the tall format that is the way databases store data:

Unpviot in Google Sheets

But how do we unpivot our data like that?

It turns out it’s quite hard.

It’s harder than going the other direction, turning tall data into wide data tables, which we can do with a pivot table.

This article looks at how to do it using formulas so if you’re ready for some complex formulas, let’s dive in…

Unpivot in Google Sheets

We’ll use the wide dataset shown in the first image at the top of this post.

The output of our formulas should look like the second image in this post.

In other words, we need to create 16 rows to account for the different pairings of Customer and Product, e.g. Customer 1 + Product 1, Customer 1 + Product 2, etc. all the way up to Customer 4 + Product 4.

Of course, we’ll employ the Onion Method to understand these formulas.


Click here to open the Unpivot in Google Sheets template

Feel free to make your own copy (File > Make a copy…).

(If you can’t open the file, it’s likely because your G Suite account prohibits opening files from external sources. Talk to your G Suite administrator or try opening the file in an incognito browser.)

Step 1: Combine The Data

Use an array formula like this to combine the column headings (Customer 1, Customer 2, etc.) with the row headings (Product 1, Product 2, Product 3, etc.) and the data.

It’s crucial to add a special character between these sections of the dataset though, so we can split them up later on. I’ve used the fox emoji (because, why not?) but you can use whatever you like, provided it’s unique and doesn’t occur anywhere in the dataset.


The output of this formula is:

Unpivot Data In Google Sheets Step 1

Step 2: Flatten The Data

Before the introduction of the FLATTEN function, this step was much, much harder, involving lots of weird formulas.

Thankfully the FLATTEN function does away with all of that and simply stacks all of the columns in the range on top of each other. So in this example, our combined data turns into a single column.


The result is:

Unpivot Data In Google Sheets Step 2

Step 3: Split The Data Into Columns

The final step is to split this new tall column into separate columns for each data type. You can see now why we needed to include the fox emoji so that we have a unique character to split the data on.

Wrap the formula from step 2 with the SPLIT function and set the delimiter to “🦊”:


This splits the data into the tall data format we want. All that’s left is to add the correct column headings.

Unpivot Data In Google Sheets Step 3

Unpivot With Apps Script

You can also use Google Apps Script to unpivot data, as shown in this example from the first answer of this Stack Overflow post.

Further Reading

For more information on the shape of datasets, have a read of Spreadsheet Thinking vs. Database Thinking.