Forum Discussion
Microsoft EmployeeDAX: 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 character to split ON, aka space in this case. I also tried :
First Name =
LEFT( 'Table1'[Name],
SEARCH(" ", 'Table1'[Name], 0,
LEN('Table1'[Name])
)
)Thank you in advance for your help!
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
16 Replies
v-qiuyu-msftCommunity Support
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 YuHi v-qiuyu-msft,
I am trying to accomplish the same thing as mentioned in the initial post; however, I am having issues with the formula you provided around the SEARCH function. It's rendered an error message stating, "The search Text provided to function 'SEARCH' could not be found in the given text". Here is an example:
The Item Description is "COMPANY PRODUCT NAME" and I need to split this up into two separate columns that read column #1 "COMPANY" and column # 2 "PRODUCT NAME". The formula I'm using is:
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],"","-")))
Any idea why this isn't working?
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?
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
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?
- fsfmendonca
Advocate I
FirstName = PATHITEM(SUBSTITUTE('fTable'[FullName];" ";"|");1)
Lastname = PATHITEMREVERSE(SUBSTITUTE('fTable'[FullName];" ";"|");1)
=PATHITEMREVERSE(SUBSTITUTE(Performance[Performance Review Rating],"-","|"),1)
thank you so much guru
- kcantor
Community Champion
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.
I am a DAX beginner, so this is probably an inefficient way to do this.
Method assuming you have a delimiter like ,. Assume the schema is LNAME, FNAME (change col names below to suit)
1. Find the position of comma, then subtract one. That is length of LNAME.
2. Trim from left start position 1 the num above.
=TRIM( //trim from left, the number of chars in FIND LEFT(Table, //this find finds the comma position from the start char on left, then subtracts one for last name length FIND(",",Table[NameCol],1)-1))3. Find Len of whole name (e.g. LNAME, FNAME), subtract 1 for the comma (I learnt spaces are not counted doing this exercise).
4. Subtract Len whole name-1 from len last name to get FNAME len
5. Trim Right on that len
=TRIM( //trim from right the name col, RIGHT(Table[NameCol], //the length of whole name LEN(Table[NameCol]) //minus the length of the last name -(FIND(",",Table[NameCol],1))-1) //to get the first name )I built this logic up over a few columns to determine length of strings, comma position etc.
