RIGHT, SEARCH, LEN only providing partial string outputs

[Anonymous]

Good day, 

I've searched the forums and the web and I'm hitting a wall with something which seems so straight forward, or I'm missing something.

The image below contains the DAX formula, the original "Text" and current "Output":

Output = RIGHT('Table'[Text], SEARCH(": ",'Table'[Text],1,LEN('Table'[Text])))

Desired outcome: I have a large data set with variable string lengths to the left of the "Text" column, which is luckily always separated by a ":" and in some of the column rows, there might be text with no pre-text separated by a delimiter, so then it should just output the entire string (as per the "correct" outputs above). 

Any help or suggestions to achieve my desired outcomes would be greatly appreciated. 

Thanks!

[amitchandak]

@Anonymous , In case you are trying to split by delimiter, better do it in Power Query, refer 

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[ryan_mayu]

@Anonymous 

please try this

Column = right('Table'[TEXT],len('Table'[TEXT])-SEARCH(":",'Table'[TEXT],1))

[Anonymous]

Hi @ryan_mayu; 

Thanks for your prompt response, your proposal works, BUT only if a delimiter is present in the column. As soon as there is no delimiter present, I receive the following error message: 

The search Text provided to function 'SEARCH' could not be found in the given text.

As part of my OP, there will be rows without text being separated by a delimiter, but I would then just want it to display in the column, as per the "Correct" elements in the image of my OP. A combination of both what is needed for a solution. 

@amitchandak, as mentioned above, simply separating by delimiter does not solve the issue as it struggles to handle the aforementioned. 

Any suggestions?

[Anonymous]

Just for clarification, below is a sample of the desired output:

[ryan_mayu · Accepted solution]

@Anonymous 

please try this

Column = 
if(SEARCH(":",'Table'[TEXT],1,0)=0,'Table'[TEXT],right('Table'[TEXT],len('Table'[TEXT])-SEARCH(":",'Table'[TEXT],1)))

[Anonymous]

That did the trick - thanks for your prompt responses @ryan_mayu !