Trying to split text into two columns with an inconsistent delimeter

[Anonymous]

Hi All,

Here's a small mock data set for a data source I am trying to clean. I need to separate the text into two separate columns, but "traditional" methods like using some basic text funtions and splitting by delimeter do not work.

I'm trying to split the column "Project Name" into two new columns:

• One column will just have the Project Number (the 4-6 digit number at the beginning of the cell)
• The other column will have the Project Name (the sting of text that comes after the number)

Here are my issues:

• Due to human error when an employee enters a project name into our system, sometimes there is an extra space after the first dash (as seen in the second row) or sometimes the first dash is omitted (see the fifth row)
• Ideally, the Project name should follow this taxonomy: "Project Name"space"-"space"Project Title" but this doesn't always happen for all instances

The Challenge:

• I can't use a Left() funtion because the project numbers vary in number of digits
• I cannot split the column by delimeter because there are a few instances that don't adhere to the "Project Name"space"-"space"Project Title" structure due to human error
• For the extra space after the dash instances, I want to avoid the new Project Name column to return the extra space as well (conceptually thinking, I want all of the text to be left-most aligned so the first thing in the cell is a character, not a space)

The EXTRA Challenge:

• This data is being pumped into Power BI directly from our CRM, and due to different permission levels, I cannot query edit. As such, I'm trying to figure out a fix by using DAX to create new columns. 

Thank you in advance for any or all insight you can offer to help me fix this very nitpicky issue!

[Anonymous · Accepted solution]

Try using the below DAX to create columns:

Project Number = 
VAR v_hyphenPos = SEARCH( "-", Data[ProjectName], 1, 0)
RETURN
TRIM( LEFT( Data[ProjectName], v_hyphenPos-1 ) )

Project Name = 
VAR v_hyphenPos = SEARCH( "-", Data[ProjectName], 1, 0)
RETURN
TRIM( RIGHT( Data[ProjectName], (LEN(Data[ProjectName])-1) - (v_hyphenPos  - 1 ) ) )

Thanks.