Hi, I have a situation where I need to split a string into 2 columns. My initial data looks like: Product NameALUM SULPHATE SOL 8% UKI 1K BLKALUMINIUM SULPHATE GRAN 25K BGALUM SULPHATE KIBBLED 17% 25K BGALUM SULPHATE PDR IRON FREE 17% 1K BLKALUM TRIHYD SH150 1LALUMINIUM TRIHYD SH300 25K BGALUMINIUM TRIHYD SH500 25K BG I need to obtain: Product NamePackageALUM SULPHATE SOL 8% UKI 1K BLKALUMINIUM SULPHATE GRAN25K BGALUM SULPHATE KIBBLED 17% 25K BGALUM SULPHATE PDR IRON FREE 17% 1K BLKALUM TRIHYD SH150 1LALUMINIUM TRIHYD SH300 25K BGALUMINIUM TRIHYD SH500 25K BG I tried different solution but no getting the expected result. Thanks, Andy

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

Regex

Custom Tools

Parse

Preparation

Accepted answers

SamJHolmes

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

Andy Solution.yxzp

All comments

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-p/17867

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!

Andy Solution.yxzp

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280