Skip to content

GS Challenge 5 – Text Functions

Welcome to your fifth Spreadsheet Life Google Sheets challenge!

In this article, I will present you with four Google Sheets questions from easy to hard, and your goal is to see how many you can solve correctly. The theme for this challenge is text functions. How many questions can you get right?

Click on the link below to download a copy of the spreadsheet.

Google Sheets Challenge 5 Spreadsheet

Question 1

For question number 1, your goal is to change each name to proper case. In other words, you want to capitalize the first letter of each word and leave all the other letters in lowercase.

Question 1 – Solution

To complete question 1, all you need to do is use the PROPER function in Google Sheets.

Select the cell next to the first name in the list, and then enter the following formula.

=PROPER(B4)

The PROPER function takes the text from the selected cell and transforms that text into proper case.

Once you’ve written the first formula, you can use the fill handle to copy the formula down for all of the remaining names.

Question 2

For question 2, your task is to reorder the names in ascending order based on the length of each name.

Question 2 – Solution

To solve question 2, you must first calculate the length of each name. You can do this by using the LEN function.

Select the cell next to the first name in the list, and enter the following formula.

=LEN(B4)

The LEN function calculates the length of the text from the referenced cell. You can now drag the formula down for the remaining names in the list.

Now that you have the length for each name, you can select all the names and length values. After that, go to Data > Sort range > Advanced range sorting options.

In the window that appears, make sure that column C is selected, and also select the A → Z option. You can then click on sort.

The names are now in ascending order by length.

Question 3

For question 3, your goal is to write a formula to get the first name from each full name and also to write a formula to get the last name from each full name.

Question 3 – Solution

You will use the INDEX and SPLIT functions to get the first name.

Select the cell next to the first full name, and enter the following formula.

=INDEX(SPLIT(B4, " "), 1)

The SPLIT function takes the text and splits it into an array (list) of text values based on the specified delimiter.

The delimiter is specified as a space (the 2nd argument), so the SPLIT function returns an array of two text values – the first and last name.

The INDEX function then takes this list of text values and returns the first value due to the one entered as the second argument for the function.

As a result, you get back the first name. You can then use the fill handle to copy the formula down for the remaining names.

Getting the last name is very similar. Select cell D5, and enter the following formula.

=INDEX(SPLIT(B4, " "), 2)

This formula works almost the same way as the previous one. The only difference this time is that a two is the second argument for the INDEX function.

The two forces the INDEX function to return the second text value from the array generated by the SPLIT function. And so you get back the last name instead of the first name.

You can now use the fill handle to copy the formula down for all the remaining names.

Question 4

For question 4, you must write two different formulas to combine the last name and first name separated by a comma and space.

Question 4 – Solution

For the first formula, you will use the CONCATENATE function in Google Sheets.

Select the cell next to the first name in the list (cell D5), and enter the following formula.

=CONCATENATE(B4, ", ", C4)

The CONCATENATE function combines:

  • The first argument – the text value in B5 (the last name)
  •  The second argument – the comma and space
  • The third argument – the text value in C5 (the last name)

And this gives you back the last name, comma, space, the first name. You can now use the fill handle to drag the formula down for the remaining names.

For the second formula, select cell E5 and enter:

=TEXTJOIN(", ", TRUE, B4:C4)

The TEXTJOIN function takes the text values in B5:C5 (3rd argument) and combines them.

Also, the TEXTJOIN function places a specified delimiter (the first argument) between these text values. In this case, that is a comma and a space.

The second argument for the TEXTJOIN function (the TRUE value) doesn’t affect the outcome in this case; however, the second argument cannot be left blank. Therefore it is included.

As a result of this formula, you get back the last name combined with the first name separated by a comma and a space. You can now use the fill handle to drag the formula down for the remaining names.

And that officially completes your Google Sheets challenge number five!

How Did You Do?

Let me know in the comments below how you did!

Were these questions too difficult, too easy, or just right? Have you ever had to use text functions like this before? I’d love to hear your thoughts!

Also, if you missed any of the other Google Sheets challenges, check out these other articles below.

Watch the Video

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top