Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Removing everything to the left of a specific letter

Highlighted
6 - Meteoroid

Hi, 

 

I have a column of data, here is an extract: 

 

D1 Crisps & Snacks Wk24 Routing 08 Jun 2019_lmissin
D1 Crisps & Snacks Wk24 Routing 08 Jun 2019_lmissin
Skincare Wk 12 Routing 18 Mar 2019_sm1walk
Oriental D57 Wk 33 Routing 10 Aug 2019_sm1walk
Oriental D57 Wk 33 Routing 10 Aug 2019_sm1walk
Oriental D57 Wk 33 Routing 10 Aug 2019_sm1walk
Oriental D57 Wk 33 Routing 10 Aug 2019_sm1walk
Oriental D57 Wk 33 Routing 10 Aug 2019_sm1walk
Oriental D57 Wk 33 Routing 10 Aug 2019_sm1walk
Oriental D57 Wk 33 Routing 10 Aug 2019_sm1walk
D91 Meal Sols Wk27 Routing 29 Jun 2019_lmissin
D68 Spirits Wk26 Routing 22 Jun 2019_lmissin
Frozen back to brief Wk 2 Routing 07 Jan 2019_sm1walk
Desserts & Baking Wk 28 Routing 08 Jul 2019_sm1walk
Desserts & Baking Wk 28 Routing 08 Jul 2019_sm1walk
Desserts & Baking Wk 28 Routing 08 Jul 2019_sm1walk
Desserts & Baking Wk 28 Routing 08 Jul 2019_sm1walk
D8 Cat Litter Wk29 Routing 13 Jul 2019_lmissin

 

I want to remove everything to the left of the first W, any ideas are appreciated.

 

Thanks

Highlighted
Alteryx
Alteryx

Hi @Nbarker1 

 

There are two ways you can do this, one using Regex, if you are familiar with this. The other is using the Text to Columns and a Formula Tool which is easier if you're not familiar with Regex!

 

 

In the top workflow using Regex - This is looking for anytime the string matches Wk + anything else after this (represented by '.+)

 

In the bottom workflow - Firstly you use a formula to Replace "Wk" with "|Wk". By inserting a "|" you can then easily use the Text to Columns tool to split the data. The Select tool then allows you to remove unwanted fields/

 

Let me know if you have any questions

 

Kind Regards

Will

Highlighted
Alteryx
Alteryx

Hi @Nbarker1 

 

In fact, there is a simpler solution than the two shown above!

 

Using a Formula Tool if you enter the syntax of your formula to be Substring([MyField],FindString([MyField]," W")+1,99999) this is going to reference your data field (Replace [MyField] with your field name, and only take the information from the W onwards as you would like.

 

This means you don't need to use Regex and reduces the number of tools within your workflow!

 

Kind Regards

Will

Alteryx Certified Partner
Alteryx Certified Partner

Hi @Nbarker1 

 

This should work -

 

Right([Field], Length([Field]) - FindString([Field], "W"))

 

FindString function brings you the position of your first "W", then take the length of your string and subtract that, that will give you the amount of characters you want to bring from the right of the string you're analyzing.

 

Cheers,

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@Nbarker1 

 

Here's my non-RegEx expression:

 

 

 

 

substring([field1], FindString([Field1], "W"))

 

 

I'll stop testing and editing my reply now.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
6 - Meteoroid

Hi Will, 

 

Thank you very much, it has worked for me.

 

 

Labels