• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Remove Text Between Delimiters – Power Query

You are here: Home / Power Query / Remove Text Between Delimiters – Power Query
remove text between delimiters power query
November 11, 2021 by Philip Treacy

This is my source data and I want to remove the percentages

source data

Specifically I want to remove all occurrences of (00%) so my data ends up like this

final data table

There's no native function in Power Query to do this so I'll have to write my own code.

Watch the Video

Subscribe YouTube

 

Download Sample Excel Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

 

The way I'm going to approach the problem is

  1. Split the text string at every )
  2. Extract the text from the beginning of each sub-string, up to the (
  3. Recombine the sub-strings

With my source data loaded into Power Query, let's get to it.

Split Text By the Right-Most Delimiter

I'm going to call the ) my right-most delimiter. I want to split the text string at every occurrence of a ) so each ) will form the end, or the right hand side, of each substring.

With the query selected, add a Custom Column, using Text.Split to split the text string at every )

split by delimiter right

Leaving that code as it is will result in an empty element in the last position of the list that Text.Split creates, so I wrap the whole thing in List.RemoveMatchingItems to remove empty strings i.e. ""

The new column contains Lists as shown here

result of first split by delimiter

Extract Text Before the Left Delimiter

Now I need to get the text before the (, I can do this with a Text.Start which extracts a string from each string in the Lists in the Custom column.

For example, Text.Start takes this Brisbane/4000 (19% and turns it into this Brisbane/4000

split by left delimiter

List.Transform then takes these new strings from Text.Start and puts them into the Lists in the Custom column, replacing the values already there. This is the result

split by right delimiter

Recombine the Sub-Strings to Get the Result

The last part is to use Text.Combine to recombine the sub-strings in the Lists to get our text string back, minus the bits we don't want.

combine text

final data table

Create a Function

That's great, I have code I can use to remove text between delimiters, but every time I want to use it, I'll have to rewrite it or modify it.

If I write a function I can reuse the function any time I like and not have to rewrite or modify any code.

To begin, I duplicate the query I just finished and rename it fx_Remove_Text_Between_Delimiters

duplicate query

Open the Advanced Editor and gaze in wonder at the code.

query code

The first thing I'll do to transform this code into a function is change the Source step to a function declaration and specify that the function takes 3 arguments called TextString, LeftDelim and RightDelim.

function step 1

Then I'll rename the #"Added Custom" steps, delete the #"Removed Columns" step, change the in statement so that step L3 is returned, and not forget to remove the comma from the end of the last step before the in.

function step 2

Then I can delete the Table.AddColumn functions from each step. I'm not adding columns to a function here so these are not needed.

function step 3

Now I can insert the function arguments into the steps

function step 4

and insert the step names in the correct places

function step 5

The last thing to do is insert a let keyword after the Source step, and add a new in Source line right at the end to say the function is returning the value of the Source step (which is the result of the L1, L2 and L3 steps).

Add some indentation to make the code layout more readable and the function is finished.

function step 6

Call the Function

After closing the Advanced Editor I can add a new column in my main query, and I'll be invoking a custom function.

invoke custom function

The new column will be called Cleaned Data, the function query is the function I just wrote, TextString is the Data column, LeftDelim is ( and RightDelim is )

invoke custom function step

Giving this new column

invoke custom function result

What If You Want to Keep the Delimiters?

This code removes the delimiters too. If you want to keep the delimiters then you need to do two things.

First, pass in an argument that tells the function if you want to keep the delimiters, or not. Second, modify the function's last line to check this argument and combine the text with or without the delimiters.

Here's what that function looks like

function to keep delimiters

Calling the function looks like this, by setting KeepDelimiters to TRUE I'm telling the function to keep the delimiters in the string it returns.

call function to keep delimiters

remove text between delimiters power query

More Power Query Custom Functions Posts

delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
grouped running totals in power query

Grouped Running Totals in Power Query

Create grouped running totals with a Power Query custom function. Simple to use and extremely fast. Example workbook with code to download.
quickly create running totals in power query

Quickly Create Running Totals in Power Query

Create running totals in Power Query using super fast queries that can process 10's of 1000's of records in the blink of an eye.
power query custom functions

Power Query Custom Functions

Create functions in Power Query to provide functionality that isn't built in - from simple things like currency conversions to scraping websites, and more.

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power QueryTag: Power Query Custom Functions
Previous Post:power query advanced editor tipsTips for Using The Power Query Advanced Editor
Next Post:Interactive Personal Finance Dashboardexcel interactive personal finance dashboard

Reader Interactions

Comments

  1. Doris Kong

    February 19, 2023 at 12:20 pm

    Thank you for the video and sharing a better way to replace delimiters easily which I previously used split/replace to achieve that. Would appreciate if you can show how to split text with delimiters in a row to multiple rows. I have received data file that combine all texts in a row separated by delimiters like “,” “.” or “/”.

    Reply
    • Philip Treacy

      February 21, 2023 at 1:32 pm

      Hi Doris,

      Please start a topic on our forum and attach your file so I can see what data you are working with.

      Regards

      Phil

      Reply
  2. David Lavery

    November 30, 2021 at 9:33 pm

    I’m trying, and struggling, to expand/adapt this to work in the case of the (..) string NOT appearing in a source row. Any pointers? Thanks

    Reply
    • Philip Treacy

      November 30, 2021 at 9:40 pm

      Hi David,

      I’m not clear what you are trying to do. Can you provide an example with some sample data?

      Regards

      Phil

      Reply
      • David Lavery

        November 10, 2022 at 9:32 pm

        Basically, what if one/some field values do NOT contain the delimiter/s (get an error otherwise)

        Reply
        • Philip Treacy

          November 15, 2022 at 12:28 pm

          Hi David,

          You can modify the step that looks for the delimiter. If the delimiter is not found, just return the original data

          = Table.AddColumn(#"Added Custom", "Custom.1", each List.Transform([Custom], each try Text.Start(_ , Text.PositionOf( _, "(")) otherwise _ ))

          try otherwise catches the error (there’s no delimiter) and replaces the error with the current item in the row from the [Data] column, specified by the _

          Regards

          Phil

          Reply
  3. Maggie Wang

    November 12, 2021 at 1:28 am

    I’ve learned tons of tricks with this video, thank you very much Phil. Just a quick query, when running the keep delimiters version of the function, the () are indeed appearing against the first three items but not the last (4th), why is this happening? Thank you.

    Reply
    • Philip Treacy

      November 12, 2021 at 10:53 am

      Thanks Maggie.

      Good spot on that, I missed it, doh.

      I’ve modified the L3 step in the function so the last set of delimiters are returned if desired. I’ve updated the Excel workbook that can be downloaded but this is the new section of code.

      Text.Combine(List.Combine({Text.ToList(Text.Combine( L2 , Text.Combine({LeftDelim, RightDelim} ))),{LeftDelim}, {RightDelim}}))

      Regards

      Phil

      Reply
      • Tim Hoogenboom

        November 19, 2021 at 7:45 am

        That’s quite a whopper. I could use further explanation. Thanks.
        L3 = if KeepDelimiters then
        Text.Combine( L2 ,
        Text.Combine(
        List.Combine(
        {
        Text.ToList( Text.Combine( L2 , Text.Combine( { LeftDelim, RightDelim } ) ) ),
        {LeftDelim}, {RightDelim}
        }
        )
        )
        )
        else Text.Combine( L2 )

        Reply
        • Catalin Bombea

          November 21, 2021 at 1:58 am

          Hi Tim,
          Your L3 step looks different than the one provided in the article, make sure you use the same code. If you are trying to change the functionality, please describe what you are trying to achieve.
          Cheers,
          Catalin

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.