Split a string into 2 columns starting from a word

[emil]

Hi,

I have a situation where I need to split a string into 2 columns. My initial data looks like:

Product Name

ALUM SULPHATE SOL 8% UKI 1K BLK

ALUMINIUM SULPHATE GRAN 25K BG

ALUM SULPHATE KIBBLED 17% 25K BG

ALUM SULPHATE PDR IRON FREE 17% 1K BLK

ALUM TRIHYD SH150 1L

ALUMINIUM TRIHYD SH300 25K BG

ALUMINIUM TRIHYD SH500 25K BG

I need to obtain:

Product Name	Package
ALUM SULPHATE SOL 8% UKI	1K BLK
ALUMINIUM SULPHATE GRAN	25K BG
ALUM SULPHATE KIBBLED 17%	25K BG
ALUM SULPHATE PDR IRON FREE 17%	1K BLK
ALUM TRIHYD SH150	1L
ALUMINIUM TRIHYD SH300	25K BG
ALUMINIUM TRIHYD SH500	25K BG

I tried different solution but no getting the expected result. 

Thanks,

Andy

[MarqueeCrew]

Countwords([fieldname]) will tell you how many words you have.

Getword[fieldname],3) will get you the fourth word. That is because it starts counting from zero.

So;

Getword[fieldname],countwords(fieldname)-2) + " " + Getword[fieldname],countwords(fieldname)-1)

Should get you your desired results.

Cheers,

Mark

[SamJHolmes]

Hi Andy,

Hope you are well.

As I was answering I saw another answer pop up so I am sure that works also!

To answer this post I stumbled across a response to another question by @jdunkerley79 here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Extract-last-n-words-from-a-string/td-...

I used his logic to answer your question.  I am sure to some RegEx pros this might be a bit long-winded but here is my solution:

It basically parses out each section of a product into its own row and then grabs the last two words per product and pulls them through if they are formatted as "package" information.

Hope this helps Andy!

[emil]

Mark,

thank you for the hint it helps a lot!!

I would like however to know if there is a way to identify a word composed of a number and a letter and set is as starting point to trim/parse. 

For example I have a number and the unit of measure in the same word with no space separator : 25K or 1L and then in any string I clean it starting with this word and move it in a new column.

Many thanks,

Andy

[emil]

Hey Sam,

it is really nicely done!

I added a regex formula at the end to clean the product name:

REGEX_Replace([Product Name], [Package], '')

Nonetheless I have a red warning and not sure why. Could please advise.

Thanks again for the help,

Andy

[SamJHolmes · Accepted solution]

Hi Andy,

I replicated your process (apologies I missed taking the package out of the product name!).

The Regex_Replace worked fine for me (REGEX_Replace([Product Name], [Package], ""), the red warning in the browse window was notifying us that there was trailing white space at the end of the product name.

I just threw in a data cleansing tool afterwards to resolve this!

Hopefully this works!

Thanks

Sam

[emil]

Sam,

thank you for your time on this solution!!!

It works well for me.

Have a nice day,

Andy