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

fsfmendonca's avatar
fsfmendonca
Icon for Advocate I rankAdvocate I
8 years ago

FirstName = PATHITEM(SUBSTITUTE('fTable'[FullName];" ";"|");1)

Lastname = PATHITEMREVERSE(SUBSTITUTE('fTable'[FullName];" ";"|");1)

  • geddadac's avatar
    geddadac
    Regular Visitor
    8 years ago

    =PATHITEMREVERSE(SUBSTITUTE(Performance[Performance Review Rating],"-","|"),1)

    thank you so much guru