Removing everything to the left of a specific letter

[Nbarker1]

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

[wdavis · Accepted solution]

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]

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]

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]

@Nbarker1 

Here's my non-RegEx expression:

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

I'll stop testing and editing my reply now.

Cheers,

Mark

[Nbarker1]

Hi Will, 

Thank you very much, it has worked for me.