Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
ZachMascetta
Regular Visitor

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. 

View solution in original post

7 REPLIES 7
roniedhaka
Frequent Visitor

hi, i need help to extract specific word/text from a string of characters - like text/word example 'wifi' or 'wi-fi' or 'AP' etc and generate a new column with only rows with that contain specific text/word from a specific column cell. lets say i have 100 rows and 8 columns. in some rows has this type of text/word in column number-4. I need to generate a new table with rows contains that informations. How?

Ioanna
Regular Visitor

Mine comes back empty . I have the same sentence written in three different ways. How can I extract only a few words?
Text.BetweenDelimiters(_, "delete inapplicable):", "2.") or Text.BetweenDelimiters([Comments], "one of the following:", "2.")).
I tried with delimiters only without the text and it won't isolate the part I want. Should I attempt something else?
Sorry I am not very familiar with M or DAX.

dunwally
Regular Visitor

How we can I scan each cell/row of multiple texts in a column and return user-specified text(s) from the cell/row in a new column?

ZachMascetta
Regular Visitor

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. 

Anonymous
Not applicable

Is there another way using "measures", I have that same issue, my data set does not allow me to add more columns...

Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.