cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
analyst31 Regular Visitor
Regular Visitor

Extract text from the middle of a string

I am trying to extract the numbers in the middle of a string and add them to a new column in my table. The number i am trying to extract is the ones that are in between two - , basically like the picture below. 

 

 

image.png

 

So in this example, i would have a new column that would have only the number 11112509

1 ACCEPTED SOLUTION

Accepted Solutions
ZachMascetta Frequent Visitor
Frequent Visitor

Re: Extract text from the middle of a string

Hello! This is a pretty straight forward process. You are going to Add a Column in the Power Query Ediotr while utilizing the Extract feature. 

 

First, go to the Data view in Power BI. Then in the ribbon at the top click on "Edit Queries". Select the table that contains the data you wish to extract. At the top of the window you should see an "Add Column" ribbon. Click on that and you'll see some options for adding new columns. Select the column you want to extract the text string from (click on the "Recordno" heading and it should highlight the whole column). Then, in the "From Text" portion of the "Add Column" ribbon, click on "Extract", then "Text Between Delimiters" (see image below).

 

Between delimiters.PNG

 

This tells the Query Editor that you want to search for two delimiters in the selected column, then extract the text from between those delimiters. You have the option of defining what these two delimiters are. Since you are searching for the text between two dashes, you will use those as both your delimiters. Enter a dash (type "-" without the quotes) as your start delimiter and as your end delimiter, then click OK. The Query Editor should add a new column to your table, with the appropriate values. 

3 REPLIES 3
ZachMascetta Frequent Visitor
Frequent Visitor

Re: Extract text from the middle of a string

Hello! This is a pretty straight forward process. You are going to Add a Column in the Power Query Ediotr while utilizing the Extract feature. 

 

First, go to the Data view in Power BI. Then in the ribbon at the top click on "Edit Queries". Select the table that contains the data you wish to extract. At the top of the window you should see an "Add Column" ribbon. Click on that and you'll see some options for adding new columns. Select the column you want to extract the text string from (click on the "Recordno" heading and it should highlight the whole column). Then, in the "From Text" portion of the "Add Column" ribbon, click on "Extract", then "Text Between Delimiters" (see image below).

 

Between delimiters.PNG

 

This tells the Query Editor that you want to search for two delimiters in the selected column, then extract the text from between those delimiters. You have the option of defining what these two delimiters are. Since you are searching for the text between two dashes, you will use those as both your delimiters. Enter a dash (type "-" without the quotes) as your start delimiter and as your end delimiter, then click OK. The Query Editor should add a new column to your table, with the appropriate values. 

analyst31 Regular Visitor
Regular Visitor

Re: Extract text from the middle of a string

Thanks! I knew there had to be a way since there is a similar way in Excel to do it. Just couldnt find it. 

ZachMascetta Frequent Visitor
Frequent Visitor

Re: Extract text from the middle of a string

No problem! Happy to help Cat Happy. Let me know if you have any other issues.