How To Use The TRIM Function In Google Sheets To Clean Your Data

The TRIM function in Google Sheets removes unwanted spaces around text.

Trim Function Example In Google Sheets

TRIM removes the leading, trailing, and repeated spaces in the text values in column A.

The formula is:

=TRIM(A2)

🔗 Get this example and others in the template at the bottom of this article.

Continue reading How To Use The TRIM Function In Google Sheets To Clean Your Data

How To Create Arrays In Google Sheets (a.k.a. Array Literals)

Arrays in Google Sheets are collections of data, consisting of rows and columns. You can use arrays in formulas in the same way that you use regular A1-type ranges.

You construct arrays in Google Sheets with curly brackets: { }

They’re also known as ARRAY LITERALS.

Continue reading How To Create Arrays In Google Sheets (a.k.a. Array Literals)

How To Create A Select All Checkbox in Google Sheets

In this post, you’ll learn how to create a Select All checkbox in Google Sheets, as shown in this image:

select All Checkbox in Google Sheets

But before we look at that, did you know there’s already a quick way of selecting all checkboxes manually?

Continue reading How To Create A Select All Checkbox in Google Sheets

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:

=RANDARRAY(10, 3)

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