Get & Transform: An Alternative to Finding the Last Delimiter with Formulas

Sometimes, our data has multiple delimiters. Finding the first delimiter with formulas has been pretty easy over the years. Finding the final delimiter hasn’t been as easy…until now. In this post, we’ll use a Get & Transform query to grab the characters after the last delimiter.

Objective

Before we jump into the mechanics, let’s be clear about our objective. Let’s say we have some data that contains delimiters. For example, the column below contains the full account, and a colon delimiter is used to identify sub-accounts.

Full account by Jeff Lenning

If you inspect the list, you’ll notice that the first account doesn’t contain a delimiter. The next few rows contain a single delimiter. The last two rows contain two delimiters.

Our goal is to create a new column that contains the lowest account, that is, the characters after the last delimiter. If there are no sub-accounts, and thus no delimiters, our column should contain the primary account.

In a previous post, we discussed a formula-based approach to do this. In this post, we’ll use a Get & Transform query instead.

Detail

We’ll accomplish this task using the following steps:

  • Create a new query
  • Transform the data
  • Return it to Excel

Let’s get started.

Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.

Create a new query

To create the new query, we simply select any cell in our full account list and click the following Get & Transform command:

  • Data > From Table

This pulls the Full Account column into the query editor, as shown below.

Query editor by Jeff Lenning

With our account list in the query editor, it is time to obtain the lowest account.

Transform the data

With the Full Account column selected, we ask Excel to split it into two columns with the following command:

  • Transform > Text Column > Split Column > By Delimiter

In the resulting dialog, we tell Excel that the delimiter is a Custom delimiter, and then specify the colon. We opt to Split At the right-most delimiter, as shown below.

Split by custom delimiter by Jeff Lenning

When we click OK, the query editor contains two columns (Full Account.1 and Full Account.2), as shown below.

Split by delimiter by Jeff Lenning

Everything looks good at this point except for the full accounts without a delimiter. When there is no delimiter, null is returned. An easy way to remedy this is to create a new conditional column. It will check to see if Full Account.2 is null, and if it is, it will use the Full Account.1 value, otherwise, the Full Account.2 value.

We add the new conditional column by selecting the following command:

  • Add Column > Conditional Column

Note: if the Conditional Column command is disabled, be sure that only a single column in the query editor is selected.

In the resulting dialog, we tell Excel the column name is Lowest, and that if the Full Account.2 column value equals null then the output is the Full Account.1 value, otherwise, it is the Full Account.2 value. This is shown below.

Conditional column by Jeff Lenning

When we click OK, our new column is looking good, as shown below.

Lowest account by Jeff Lenning

Now, we can pick and choose which columns to return to Excel. Since we want to return only the Lowest column, we right-click its header and select Remove Other Columns. The result is shown below.

Pick return column

With our desired column selected, it is time to return the data to Excel.

Return it to Excel

If we want to return the lowest account column to a specific place in our workbook, we can use the Home > Close & Load To command. If we want to return the data to a new worksheet, we can use the Close & Load command. The data is returned to our workbook, as shown below.

Return to Excel by Jeff Lenning

The best news is that next period, if any new accounts are added, we can simply right-click and refresh to instantly update the results table.

If you have any alternatives to finding the last delimiter, please share by posting a comment below.

Resources

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

Leave a Comment