Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Split a string into 2 columns starting from a word

emil
7 - Meteor

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 NamePackage
ALUM SULPHATE SOL 8% UKI 1K BLK
ALUMINIUM SULPHATE GRAN25K 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

6 REPLIES 6
MarqueeCrew
20 - Arcturus
20 - Arcturus
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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
SamJHolmes
6 - Meteoroid

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:

Solution Emil.png

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
7 - Meteor

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
7 - Meteor

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
6 - Meteoroid

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!

Regex Replace.png

 

 

Hopefully this works!

 

Thanks

 

Sam

emil
7 - Meteor

Sam,

 

thank you for your time on this solution!!!

 

It works well for me.

 

Have a nice day,

Andy

Labels