4 ways to extract sort characters from data when they aren’t at the beginning of the value in Excel

Sorting in Microsoft Excel couldn’t be easier. You click somewhere inside the column and click an option in the ribbon. It couldn’t be easier as long as Excel can sort by the first few characters in each value. If the characters by which you want to sort aren’t at the beginning of the value, you’ll have to add a few steps. In this article, I’ll show you four ways to extract, or split, sort characters from the rest of the value, so you can sort by those results.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

Within this context, sort character refers to the specific characters by which you want to sort. In addition, I’ll use the terms extract and split to describe the process.

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version. For your convenience, you can download the demonstration .xlsx and .xls files. However, only the RIGHT() function is available in the menu versions (.xls). In addition, the other features aren’t all available in all ribbon versions (.xlsx). Excel for the web supports the first three sections.

How to use Flash Fill in Excel

Perhaps the easiest way to populate a new column with sort characters is to use Excel’s Flash Fill feature (add to Excel 2013). Simply enter the first set of sort characters and then click Flash Fill. If it doesn’t work, enter a second set. This feature learns from your input

Now, let’s suppose that you want to sort the records shown in Figure A by the last two digits in the department values. To demonstrate how Flash Fill can help you, do the following:

1. Enter HR in F3.
2. Click the Data tab and then click Flash Fill in the Data Tools group.

Figure A

As you can see, the feature populates this column correctly. You can easily sort by column F for the correct sort order.

For the most part, this feature handles some inconsistencies. For instance, if the sort characters at the end of the entries in column E had 1, 2 or even 3 characters, Flash Fill would still work if you want all the characters to the right of the hyphen. This won’t always be the case, but you won’t waste much time trying.

I won’t prompt you to resort and sort the data each time we try a different way to split the last two characters from the department values. Nor will I do so in figures. However, feel free to do so on your own.

How to use Text to Column in Excel

Another simple feature is Excel’s Text to Column, which lets you quickly divide values. This feature was added to Excel 2007, so it’s been around for a long time. It’s quick, but it will overwrite the original values. When you need to keep that value intact, work with a copy, which is what we’ll do. We’ll use the hyphen character as the delimiter, which means, this feature will need three columns—three segments, three columns. For example, this feature will split 101-T-AT into three separate values, 101, T and AT.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

First, insert three columns between the department and flash column. You might be wondering why I didn’t start with this solution, making it unnecessary to worry to about inserting columns. I did this on purpose specifically for that reason: You need to know that this feature needs empty columns to the right to extract the delimited segments.

Next, copy the department values to the first new column to the right. Now, let’s use Text to Column as follows:

1. Select F3:F13—the data you want to split.
2. On the Data tab, click Text to Columns in the Data Tools group.
3. In the first pane, do nothing but click Next. We want to split by using delimiters.
4. In the next pane, click Other and enter –, as shown in Figure B. Make sure to uncheck any other checked options. Data Preview shows how the data will split, so you can check your choices. Click Finish. If you click Next instead, don’t change anything in the next pane before clicking Next to get to the last pane, and click Finish.

Figure B

Figure C shows the split results. You don’t need the first two columns, so you can delete them. Nor does it matter that the first values in column F aren’t formatted correctly. At this point, you could sort by the results of the Text to Column feature in column G (after deleting the other two columns).

Figure C

If you’re working in the menu version (.xls), Flash Fill and Text to Column aren’t supported, but you can use a function.

How to use the RIGHT() function in Excel

Excel’s RIGHT() function extracts characters from the right of a string value using the syntax

RIGHT(text, [num_chars])

where text is required and identifies the string from which you want to extract characters. The optional num_chars specifies how many characters to extract and must be greater than or equal to 0. If you omit this argument, RIGHT() extracts only one character.

Now, let’s use Excel’s RIGHT() function to extract the last two characters from each value. To do so, enter =RIGHT([@Department],2) into H3 and copy to the remaining cells. As you can see in Figure D, the function returns the last two characters. Also note that the function uses structured referencing because the data is stored in a Table. If you’re working with a data range, enter the function =RIGHT(H3:H14,2) instead.

Figure D

These first three methods all work in an Excel sheet. Now let’s look at a method for Power Query.

How to use Power Query in Excel

With Power Query you access and reshape data. The truth is you don’t need Power Query to get this job done. However, if you’re collaborating or working with Power BI, you’ll want to use Power Query to prepare the data. If this is your first experience with Power Query, don’t worry—it’s easier to use than you might think.

In Excel 2016 and later, access to Power Query is part of the user interface. Earlier versions have access through an add-in. It isn’t supported by the menu version (.xls).

To get started, load the data into Power Query as follows:

1. Click anywhere inside the Table. If your data isn’t a Table object yet, Power Query will prompt you to convert it. You must do so to work with Power Query.
2. Click the Data tab.
3. In the Get & Transform Data group, click From Table/Range. Doing so will open Power Query with your data.
4. Click the Department header cell to select that column.
5. Click the Add Column tab.
6. Click Extract in the From Text group and choose Last Characters from the dropdown (Figure E).

Figure E

7. Enter 2, the number of characters to extract (Figure F) and click OK.

Figure F

Figure G shows the results.

Figure G

Now you have a column of the sort characters by which you can sort. To return the new values to Excel, so you can work with them at the sheet level, click Close & Load in the Close group on the File tab. You can see the new table in Figure H. Even if you don’t need Power Query now, it’s good to know how to accomplish simple tasks using it.

Figure H

When extracting characters from a string, for any reason, one of these four methods should get the job done.