search word and use it as slicer

[Hidrolix]

Hi all,

I try to find a word in a string that is delmited by a "space".

Since I use direct query, I can't use the delmiter from the power query editor.

The string contains always 2 parts.

1st part is management domain (MD)

2nd part is availability zone 

Example of string: Testlab Blue

I already got an first kolumn for the slicer working. As in the example the "testlab".

Beforespace =
var _searchspatie=SEARCH(" ",'CustomField MD'[MD],1,0)
Return
IF(_searchspatie=0,'CustomField MD'[MD],LEFT('CustomField MD'[MD],_searchspatie-1))

Now I created the for the 2nd part, so after the space:

Afsterspace =
var _searchspatie=SEARCH(" ",'CustomField MD'[MD],1,0)
Return
IF(_searchspatie=0,'CustomField MD'[MD], RIGHT('CustomField MD'[MD],_searchspatie-1))

This returns now: "lab red" or "ab blue"

MD = string that contains info

before space = text before space

after space = text after space (not working)

Can someone help me to make it work ? 

[kleigh · Accepted solution]

RIGHT takes a number of characters as an argument, not a string position. So you need to subtract the found position from the string's whole length.