cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: DAX: How to Split (left) a text column on Character (space)?

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

14 REPLIES 14
Highlighted
Super User I
Super User I

Re: DAX: How to Split (left) a text column on Character (space)?

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!




Highlighted
Community Support
Community Support

Re: DAX: How to Split (left) a text column on Character (space)?

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

Highlighted
Regular Visitor

Re: DAX: How to Split (left) a text column on Character (space)?

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?

Highlighted
Community Champion
Community Champion

Re: DAX: How to Split (left) a text column on Character (space)?

@amtanner

 

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




Lima - Peru
Highlighted
Regular Visitor

Re: DAX: How to Split (left) a text column on Character (space)?

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

Highlighted
Community Champion
Community Champion

Re: DAX: How to Split (left) a text column on Character (space)?

@amtanner

 

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

 

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




Lima - Peru
Highlighted
Regular Visitor

Re: DAX: How to Split (left) a text column on Character (space)?

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

Highlighted
Community Champion
Community Champion

Re: DAX: How to Split (left) a text column on Character (space)?

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
Highlighted
Regular Visitor

Re: DAX: How to Split (left) a text column on Character (space)?

@Vvelarde Thank you!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors