cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SarWal
Microsoft
Microsoft

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 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!

 

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community 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]," ","-")))

 

q1.PNG

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

15 REPLIES 15
TimMarsh
Frequent Visitor

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.

fsfmendonca
New Member

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

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

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

thank you so much guru

v-qiuyu-msft
Community Support
Community 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]," ","-")))

 

q1.PNG

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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 @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?

Vvelarde
Community Champion
Community Champion

@amtanner

 

The COMPANY PRODUCT NAME column have a "-" ?. The error that indicates is that cant' found this - in your column




Lima - Peru

I had to do an if/else for some reason.

 

IF(the value contains ":", mid(the value, ":"), "")

 

Otherwise it puked as you described.

@Vvelarde Got it, that makes sense. So say that, using the same example, the Item Description is "COMPANY NAME PRODUCT NAME" and I need to split this up into two separate columns that read column #1 "COMPANY NAME" and column # 2 "PRODUCT NAME". Right now the formula is working using the fix you suggested, but it is splitting it up so that coulmn #1 reads "COMPANY" and column # 2 reads "NAME PRODUCT NAME". Hopefully that makes sense.

Vvelarde
Community Champion
Community Champion

@amtanner

 

You need to start to search after find the first space:

 

SEARCH(" ",column,SEARCH(" ";column)+1)




Lima - Peru

@Vvelarde

 

Here is the current string I'm using:

 

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],""," ")))

 

Using this string, it will take the text "WORKFORCE READY TIME KEEPING" and transform it into column #1 "WORKFORCE" and column #2 "READY TIME KEEPING".

 

Given your suggestion, where would the revised string SEARCH(" ",column,SEARCH(" ";column)+1) fit into the above string?

 

I have tried: Module Type = RIGHT(SUBSTITUTE(WFR_New_Module_View[Item Description],""," "),LEN(SUBSTITUTE(WFR_New_Module_View[Item Description],""," ")) - SEARCH(" ",column,SEARCH(" ";column)+1)))

 

...but I receive an error message. Thoughts?

Vvelarde
Community Champion
Community Champion

hi @amtanner

 

Replace 

 

SEARCH(" ",column,SEARCH(" ";column)+1

 

to 

 

SEARCH(" ",WFR_New_Module_View[Item Description],SEARCH(" ";WFR_New_Module_View[Item Description])+1

 

 

 




Lima - Peru

@Vvelarde Thank you!

kcantor
Community Champion
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.

Capture2.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.