How to Extract Delimited Data Using Excel Power Query
Your email has been sent
Learn how to use Excel Power Query’s extract and split column features to extract delimited strings into their components with this step-by-step tutorial.
You might receive foreign data in the form of characters strung together that you must import into Microsoft Excel. Generally, you’ll import the full dataset, even if you require only a portion of the string.
For instance, you might receive a list of transaction numbers, which in part contain the customer identification number. You need only the customer portions in Excel to create a relationship between that customer and a table that contains the customer names. That way, when reporting, viewers will see the customer names and not a meaningless number.
When this is the case, you can use Power Query’s Extract and Split Column features to extract delimited strings into their components. I’m using Microsoft 365 Desktop and Power Query in Microsoft Excel. Power Query is available in older versions through Excel 10. You can download the Microsoft Excel demo file for this tutorial.
Jump to:
You can use Excel string functions, Text to Columns or Flash Fill, but here are reasons you might not:
If the data is in Excel, you might use functions or formulas, but unless you’re an expert, that will take a bit of time. Most of us can’t just rattle off the necessary syntax and get it right the first time. Power Query is quick and requires no specialized knowledge of Excel functions.
We’ll work with a simple Excel sheet with a few delimited strings in a Table named TableCustomerID. You don’t have to replace the default Table name, but meaningful names are easier to work with if you have multiple Tables. However, the data must be formatted as a Table object. If it isn’t, Power Query will prompt you to convert the data range.
SEE: Here’s how to create and populate a table in Microsoft Excel’s Power Query.
Let’s suppose you have a list of customer identification numbers with three sections each. Furthermore, a hyphen character serves as a delimiter between the three sections (Figure A). You want to use the middle component of each string because that’s the section that actually identifies each customer. The other two components identify the region where the customer resides and a transaction number.
Figure A
The first step is to load the data into Power Query as follows:
1. Click anywhere inside the Table.
2. Click the Data tab.
3. In the Get & Transform Data group, click From Table/Range.
That’s it. The simple Table shown in Figure A is now in Power Query.
With the data in Power Query, you can start extracting sections.
There are really two ways to extract data in Power Query. We’ll begin by using Extract options, which returns a subset of the original value. To begin:
Figure B
Figure C
As you can see in Figure D, this option returns only the first character(s) before the delimiter.
Figure D
To reclaim the original data, delete the Extracted Text Before Delimiter step in the Applied Steps pane shown in Figure E by right-clicking the item and choosing Delete from the contextual menu.
Figure E
SEE: How to find duplicates using Microsoft Power Query.
Now, let’s do the same thing with the next option, Text After Delimiter. When prompted, enter the hyphen character and click OK to see the results shown in Figure F. This time, Power Query removes the first two characters, the first number and the first hyphen.
Figure F
Once again, reclaim the original data by deleting the extract step and then choosing the Text Between Delimiters option. This time, Power Query prompts for two delimiters. In this case, they’re both the hyphen character (Figure G).
Figure G
Click OK to see the results shown in Figure H.
Figure H
Now, we’ll look at another way to divide the three sections of each string, but we won’t extract pieces from the strings, we’ll split the strings. Reclaim the original data before you continue.
Power Query’s Split Column helps you return more than a single piece of the string. For instance, let’s suppose you want three columns of data, one for each section. To accomplish this, use Split Column as follows:
Figure I
Figure J
This option separates each string into three columns using the delimiter character to determine where each section begins and ends.
As you can see, both Extract and Split Column help you separate data quickly. You’re likely to run into uses for both.
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to Extract Delimited Data Using Excel Power Query
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply