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

kcantor's avatar
kcantor
Icon for Community Champion rankCommunity Champion
10 years ago

Have you considered doing this in Query Editor? You can simple select the column, select split column from the ribbon and choose split by delimiter. Then select the space.

It will create two new columns.