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

schang's avatar
schang
Regular Visitor
9 years ago

Hi,

 

Your solution is great and helpful to me. Do you have any idea if there are first mid last name of someone and you only want to get the last name?

 

For example, if there is a text called "John George Washington Bosh Wang" and you only want to get the Wang out, do you have any idea?

Anonymous's avatar
Anonymous
Not applicable
9 years ago

An elegant solution for navigating delimiters in Vertipaq is to leverage the PATHITEM() and PATHLENGTH() functions using SUBSTITUTE().

 

For example, if your delimiter was "." and you wanted to return "Simon" (the 6th element) from "Hello.Friend.My.Name.Is.Simon.Nuss":

 

PATHITEM( SUBSTITUTE( [Column1], ".", "|" ), 6 )

If you want to return the last occurance, i.e. "Nuss", you can perform:

Result = 
    VAR Nodes = SUBSTITUTE( [Column1], ".", "|" )
RETURN
    PATHITEM( Nodes, PATHLENGTH( Nodes ) )

If you want to return the 3rd last occurance, i.e. "Is", you can perform:

Result = 
    VAR Nodes = SUBSTITUTE( [Column1], ".", "|" )
RETURN
    PATHITEM( Nodes, PATHLENGTH( Nodes ) - 3 )

 

Good luck!

Simon