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 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.
- Vvelarde
You need to start to search after find the first space:
SEARCH(" ",column,SEARCH(" ";column)+1)
Here is the current string I'm using:
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],""," ")))
Using this string, it will take the text "WORKFORCE READY TIME KEEPING" and transform it into column #1 "WORKFORCE" and column #2 "READY TIME KEEPING".
Given your suggestion, where would the revised string SEARCH(" ",column,SEARCH(" ";column)+1) fit into the above string?
I have tried: Module Type = RIGHT(SUBSTITUTE(WFR_New_Module_View[Item Description],""," "),LEN(SUBSTITUTE(WFR_New_Module_View[Item Description],""," ")) - SEARCH(" ",column,SEARCH(" ";column)+1)))
...but I receive an error message. Thoughts?