Need help with a formula to show certain text in a column

[dsabsi]

 Hello gentlemen and ladies, 

I need your help with perhaps a simple formula. 

I have text in a column in Power BI Desktop, and I only want to show a certain piece of this text column. 

To simply this, I added the below picture, where I only want to see "Hello". 

I'm aware that I have to create a new column for this but I can't find the formula to accomplish this. 

 Can anyone please help?

Thanks in advance. 

Kind regards, 

DSabsi

[v-shex-msft]

Hi @dsabsi,

You can try to use below formula to get specific part to text:

Display = RIGHT([Text],LEN([Text])-SEARCH(":",[Text],,0)) 

Regards,

Xiaoxin Sheng

[dsabsi]

Hello @v-shex-msft@MFelix@Salvador,

Many thanks for your replies (this community rocks!)

HelpfromPowerBIfriends= right(tablename];len(tablename)-search("Pakketnaam";tablename;;0))

I tried your formula but it doesn't show me what I want to see... I think we are very close. 

See my attached file, I want to see the line "Pakketnaam ZVW basis, variant natura" from that long text field. 

It's somewhere on the 70th position from the left, and I only want to see that text and not that text behind it.

Do you have any idea how we can achieve this in Power BI? 

Thanks in advance guys!

Cheers

Dsabsi

 Hop

[v-shex-msft · Accepted solution]

Hi @dsabsi,

You can try to use below formula:

Search = 
var right_range= "Registratienummer"
var left_range= "Pakketnaam:"
var temp=LEFT([Test],SEARCH(right_range,[Test],,0)-1)
return
RIGHT(temp,LEN(temp)-SEARCH(left_range,temp,,0)-LEN(left_range))

Regards,

Xiaoxin Sheng

[dsabsi]

Hello @v-shex-msft , 

Many thanks for your swift reply (i'm sorry that I'm just responding, I was off for a few days). 

I tried your formula but I got an error: An argument of function 'Search' has the wrong data type or has an invalid value. 

Do you have an idea of what i'm doing wrong?

I added the printscreen in this message (I only removed the column name)

Many thanks!!!!

CHeers,

DSabsi 

[dsabsi]

Hello @v-shex-msft,

If I remove the "]", it won't get the column in my table database. 

I've added a part of my column with only the crucial parts removed, so that you can see where it gets the data from. 

 I look forward to your solution, it would be great if we can solve this 🙂

Cheers, 

Dsabsi

[v-shex-msft]

Hi @dsabsi,

I found you left the "]" in the formula, I think the issue is related to it, please modify it to fix the issue.

Regards.

Xiaoxin Sheng

[MFelix]

Hi @dsabsi,

You need to use a combination between search and left something like this:

Split = LEFT( Table[Column]; 
           SEARCH(":";Table[Column]; 1; 
               LEN(Table[Column])-1
             )-1
         )

In this formula I'm assuming you want everything from the ":" forward, don't know exactly how you have your information but should work.

Regards,

MFelix

[Salvador]

Hi,

You could use the "left" function, if the text has a constant number of characters, or... there's a post on how to extract the data between special characters, that might be of help. http://community.powerbi.com/t5/Desktop/How-to-extract-the-data-between-special-characters/td-p/1357...