Pull 2 words separated by dashes to make new Column

[unclejemima]

I've got some items in Inventry[part] where I need to make a new column based off the part number.

Example of part numbers...

55-D-Kaffee-German-L-Hot
55-D-Kaffee-German-M-Hot
55-D-Kaffee-Italian-L-Hot
55-D-Kaffee-Italian-L-Ice
55-D-Kaffee-Italian-M-Hot
55-D-Kaffee-Italian-M-Ice
55-D-MilchK-7Layer-L-Gel
55-D-MilchK-7Layer-L-Hot
55-D-MilchK-7Layer-L-Ice
55-D-MilchK-7Layer-M-Gel
55-D-MilchK-7Layer-M-Hot
55-D-MilchK-7Layer-M-Ice
55-D-MilchK-Cappuc-L-Hot
55-D-MilchK-Cappuc-M-Hot

I'm trying to make a new column where it will extract the last 2 words between the "-"

Example, if the code is 55-D-MilchK-Cappuc-M-Hot, I want to extract MilchK-Cappuc

Any creative way I could do that in a new column???  The number of characters is not always the same, but there is always dashes separating the words.

Please and thank you!

[ChrisMendoza]

@unclejemima,

Does this work for your entire dataset?

=Text.BetweenDelimiters([PartNumber],"-","-",1,1)

It seems to work for your small sample:

[unclejemima]

When trying...

=Text.BetweenDelimiters([PartNumber],"-","-",1,1)

...I get an error.  My dataset has a bunch of values where the Part is 9 digits.  Perhaps its throwing an error because there is parts that don't have a delim.

Is there a way to mod the code to it will not error on parts that don't have a - as a delim?

[ChrisMendoza · Accepted solution]

@unclejemima,

You could try otherwise:

try Text.BetweenDelimiters([PartNumber],"-","-",1,1) otherwise Text.BetweenDelimiters([PartNumber],"-","-",1,1)

It should allow you to find the errors, then you can analyse the reason why it failed and modify your Add Column.

[unclejemima]

Is that in the query editor or just a calculated column formula?

[ChrisMendoza]

Query Editor Text.BetweenDelimiters( )

[zapps]

Hi @unclejemima,

In your Edit Query follow these steps:

1) Highlight the column you want to separate.

2) Click on "Add Column" tab from your ribbon.

3) Click on the Drop Down for "Column from examples" and select "From Selection"

4) In your first line, type Kaffee-German and press enter. 

This should auto fill the rest of your cells to pull the information you want.

Hope this helps!

zapps

[unclejemima]

This is cool...let me try!

[unclejemima]

I must be doing something wrong...not working.  See screenshot...

[unclejemima]

I'm assuming its because there are other (many tens of thousands of codes) that don't start with "55-D"

Perhaps I need to make a column that shows them only shows the parts if they start with "55-D"...and then I can apply the "Add Column>from Example> from Selection?"

Thoughts? 

[rajendran]

You can right clikc your column header in your Query Editor, select Split column -> by delimiter, Enter the details to split ( Delimiter : -, split at "Each occurrance of the delimter" ). This will create new columns based on your data, keep the required column, delete other columns, you can filter based on this column as needed.

Thanks
Raj

[unclejemima]

Am I doing this correct?  Is my deliminator :-,

[Vvelarde]

@unclejemima

Hi, try with this Extract -Text Between Delimiters

Regards

Victor

[unclejemima]

Got this to work by selecting custom deliminator, putting simply - as the deliminator and leaving all as default.

But what happend to my Part column...i was hoping it would leave Part alone and give me as well Part.1, Part.2, etc.  I can't find the original Part column.  

Is there a way to split the column, but leave the original column alone?