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

amtanner's avatar
amtanner
Regular Visitor
10 years ago

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's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
10 years ago

amtanner

 

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

  • amtanner's avatar
    amtanner
    Regular Visitor
    10 years ago

    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's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      10 years ago

      amtanner

       

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

       

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

      • amtanner's avatar
        amtanner
        Regular Visitor
        10 years ago

        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?

  • mateoc15's avatar
    mateoc15
    Icon for Advocate II rankAdvocate II
    5 years ago

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

     

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

     

    Otherwise it puked as you described.