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.
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.
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.
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.
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.
When we click OK, the query editor contains two columns (Full Account.1 and Full Account.2), as shown below.
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.
When we click OK, our new column is looking good, as shown below.
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.
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.
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.
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.