Skip to content

Email Newsletter Challenge 18

This week’s challenge will require you to sort some data in an unconventional way.

If that sounds interesting to you, then download the spreadsheet challenge using the links below.

Challenge

Your task is to sort the names by length – shortest to longest.

Solution – Excel for Office 365

Use the following formula to sort the names.

=SORTBY(B4:B10, LEN(B4:B10), 1)

The SORTBY function sorts the first argument (B4:B10) by referencing the array generated in the second argument. The second argument utilizes the LEN function to get an array of lengths for each name.

The SORTBY function looks at these numbers and uses them to sort the first argument (B4:B10). The 1 used as the third argument forces the SORTBY function to sort the data in ascending (smallest to largest) order.

This is the quickest way to sort the data in Excel for Office 365.

Solution – Older Versions of Excel

Since the SORTBY function is not available in older versions of Excel, you have to take a different approach to this problem. Start by selecting the cell next to the first name, and enter the following formula.

=LEN(B4)

This calculates the length of the first name. Your next step is to copy the formula down with the fill handle.

You can then select the names and numbers, right-click, and go to Sort > Custom Sort.

In the box that appears, sort by Column C. Then click Ok.

You can then copy and paste the sorted names where they need to go.

Solution – Google Sheets

Use the following formula to sort the names.

=SORT(B4:B10, LEN(B4:B10), TRUE)

The SORT function sorts the first argument (B4:B10) by referencing the array generated in the second argument. The second argument utilizes the LEN function to get an array of lengths for each name.

The SORT function looks at these numbers and uses them to sort the first argument (B4:B10). Finally, the TRUE used in the third argument forces the SORT function to sort the data in ascending (smallest to largest) order.

This is the quickest way to sort the data in Google Sheets.

I hope you had fun, felt challenged and learned something new!

And if you’re not signed up for my weekly email newsletter, you can sign up right here!

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