Trimming SKU suffixes

[kaitturner]

I have a list of Item numbers that I need broken down to their root SKUs.  Some have suffixes, some have prefixes, others do not.  I need help determining how to trim only those with suffixes within the list.  

Example:

Item Number	Root SKU
100260	100260
100267	100267
100268	100268
100270	100270
100270W	100270
100276	100276
100276P	100276
280591	280591
280591A	280591
PRM2840	PRM2840
TCK1524	TCK1524
WHU4016	WHU4016
WHU4059	WHU4059
WHU4072	WHU4072
WTI0956	WTI0956
WTI0957	WTI0957
XE4494	XE4494
XM0101	XM0101
XM0120	XM0120

[MarqueeCrew]

If you want just the numbers you can try the text cleanse tool or use this formula:

regex_replace([sku],"\u", '')

 cheers,

mark

[Ladarthure]

Hi @kaitturner,

to do what you aksed I would use a find replace tool, supposing you have a database of all "expected" SKU, find a workflow attached with an example with your data!

[AdamR_AYX · Accepted solution]

Here is another formula option without regex 🙂

TrimRight([Item Number],"ABCDEFGHIJKLMNOPQRSTUVWXYZ")

[MarqueeCrew]

@kaitturner ,

I'm sorry that I responded while walking.  My friend @AdamR_AYX is making me train for a marathon.  He's out of his mind!  But he's the real-thing here at Alteryx.  He's the person responsible for my RegEx exposure.

When Adam gives you a STRING function (as below) it not only is easy to understand, but also it runs faster than a regular expression.  It should run about 3+ times faster (for that function)

If you tried to pattern match this, you could use:

REGEX_Replace([Item Number], "(\u*\d*)[a-zA-Z]*$", '$1')

So if you're feeling generous and want to try a regular expression as an alternative, here it is.  Otherwise remember that Chaos reigns within.

Cheers,

Mark