Forum Discussion

SarWal's avatar
SarWal
Icon for Microsoft Employee rankMicrosoft 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's avatar
    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

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
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

Dante_Pastor's avatar
Dante_Pastor
New Member
2 years ago

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?