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

Removing everything to the left of a specific letter

Nbarker1
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

5 REPLIES 5
wdavis
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

wdavis
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

Thableaus
17 - Castor
17 - Castor

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,

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
Nbarker1
6 - Meteoroid

Hi Will, 

 

Thank you very much, it has worked for me.

 

 

Labels