Forum Discussion

Microsoft Employee

10 years ago

Solved

DAX: How to Split (left) a text column on Character (space)?

Hello, I'm trying to split "Name" into "First Name" and "Last Name". How do I do this? I found the DAX function "left", but you have to provide a character count to split on, rather than a charac...

v-qiuyu-msft
10 years ago
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

amtanner

Regular Visitor

9 years ago

Vvelarde

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?