Forum Discussion
Microsoft EmployeeDAX: How to Split (left) a text column on Character (space)?
Hi SarWal,
In your scenario, as you want to split a column based on space rather than a character, you need to replace the space with a character use SUBSTITUTE() function, then split the value use Search() function. Please refer to screenshots below:
First name = LEFT(SUBSTITUTE(Table1[Name]," ","-"),SEARCH("-",SUBSTITUTE(Table1[Name]," ","-"))-1)
Last name = RIGHT(SUBSTITUTE(Table1[Name]," ","-"),LEN(SUBSTITUTE(Table1[Name]," ","-"))-SEARCH("-",SUBSTITUTE(Table1[Name]," ","-")))
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
Community SupportHi SarWal,
In your scenario, as you want to split a column based on space rather than a character, you need to replace the space with a character use SUBSTITUTE() function, then split the value use Search() function. Please refer to screenshots below:
First name = LEFT(SUBSTITUTE(Table1[Name]," ","-"),SEARCH("-",SUBSTITUTE(Table1[Name]," ","-"))-1)
Last name = RIGHT(SUBSTITUTE(Table1[Name]," ","-"),LEN(SUBSTITUTE(Table1[Name]," ","-"))-SEARCH("-",SUBSTITUTE(Table1[Name]," ","-")))
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
Hi v-qiuyu-msft,
I am trying to accomplish the same thing as mentioned in the initial post; however, I am having issues with the formula you provided around the SEARCH function. It's rendered an error message stating, "The search Text provided to function 'SEARCH' could not be found in the given text". Here is an example:
The Item Description is "COMPANY PRODUCT NAME" and I need to split this up into two separate columns that read column #1 "COMPANY" and column # 2 "PRODUCT NAME". The formula I'm using is:
Module Type = RIGHT(SUBSTITUTE(WFR_New_Module_View[Item Description],"","-"),LEN(SUBSTITUTE(WFR_New_Module_View[Item Description],"","-"))-SEARCH("-",SUBSTITUTE(WFR_New_Module_View[Item Description],"","-")))
Any idea why this isn't working?
- Vvelarde
Community Champion
The COMPANY PRODUCT NAME column have a "-" ?. The error that indicates is that cant' found this - in your column
Vvelarde Got it, that makes sense. So say that, using the same example, the Item Description is "COMPANY NAME PRODUCT NAME" and I need to split this up into two separate columns that read column #1 "COMPANY NAME" and column # 2 "PRODUCT NAME". Right now the formula is working using the fix you suggested, but it is splitting it up so that coulmn #1 reads "COMPANY" and column # 2 reads "NAME PRODUCT NAME". Hopefully that makes sense.
Hi,
Your solution is great and helpful to me. Do you have any idea if there are first mid last name of someone and you only want to get the last name?
For example, if there is a text called "John George Washington Bosh Wang" and you only want to get the Wang out, do you have any idea?
An elegant solution for navigating delimiters in Vertipaq is to leverage the PATHITEM() and PATHLENGTH() functions using SUBSTITUTE().
For example, if your delimiter was "." and you wanted to return "Simon" (the 6th element) from "Hello.Friend.My.Name.Is.Simon.Nuss":
PATHITEM( SUBSTITUTE( [Column1], ".", "|" ), 6 )
If you want to return the last occurance, i.e. "Nuss", you can perform:
Result = VAR Nodes = SUBSTITUTE( [Column1], ".", "|" ) RETURN PATHITEM( Nodes, PATHLENGTH( Nodes ) )If you want to return the 3rd last occurance, i.e. "Is", you can perform:
Result = VAR Nodes = SUBSTITUTE( [Column1], ".", "|" ) RETURN PATHITEM( Nodes, PATHLENGTH( Nodes ) - 3 )Good luck!
Simon
Hi, good afternoon. I know this is really old, but DAX doesn't work for me if I use negative values like -1 but it does if I apply +1, also with the Right formula since I', using"-" for the search formula. Do you know why is this happening?
