Manual Working Results Details LenPrefixBaseSuffixWXXSBA 60WXXSBA38xxxxS 7 3xxxxxSN805X44S 8 N805XXXSD2TZ119XX 9D2TZ11XXXF3DC733XXX 10F3DC7335XXXJX7A22053UA 11JX7A22053UAML3B600C06EE 12ML3B600C06EEFT1T14K733ZDD 13FT1T14K733ZDD9L147202BD3JA6 149L147202BD3JA6DC343F832DC35B8 15DC343F832DC35B88A5313E709AA35B8 168A5313E709AA35B8NL3B1564417GC31Y6 17NL3B1564417GC31Y6DA5354237W21AE3DN1 18DA5354237W21AE3DN1FL3B18519A58LAF3ZHE 19FL3B18519A58LAF3ZHEAMHC3J99501A42AA52CP 20AMHC3J99501A42AA52CP

How to drive Prefix, base and suffix for the part number - Base should start with Number

	Manual Working Results

Details	Len	Prefix	Base	Suffix
WXXSBA	6	0	WXX	SBA
38xxxxS	7		3xxxxx	S
N805X44S	8		N805XXX	S
D2TZ119XX	9	D2TZ	11XX	X
F3DC733XXX	10	F3DC	7335X	XX
JX7A22053UA	11	JX7A	22053	UA
ML3B600C06EE	12	ML3B	600C06	EE
FT1T14K733ZDD	13	FT1T	14K733	ZDD
9L147202BD3JA6	14	9L14	7202	BD3JA6
DC343F832DC35B8	15	DC34	3F832	DC35B8
8A5313E709AA35B8	16	8A53	13E709	AA35B8
NL3B1564417GC31Y6	17	NL3B	1564417	GC31Y6
DA5354237W21AE3DN1	18	DA53	54237W21	AE3DN1
FL3B18519A58LAF3ZHE	19	FL3B	18519A58	LAF3ZHE
AMHC3J99501A42AA52CP	20	AMHC3J	99501	A42AA52CP

Alteryx Editions

Accepted answers

alexnajm

@MaheshR I gave enough information to solve this problem. I will give the formulas, but not the workflow since someone with Core certification should be able to work with formulas. Please try it yourself. you can come back once you show us what you've tried yourself.

LEFT([Details],4)

SUBSTRING([Details], 4, 4)

RIGHT([Details],2)

If you care about the length, you can add these into an IF statement with IF Length([Details])>=10 THEN ... ELSE ... ENDIF

Good luck!

All comments

alexnajm

You need to give us more information to help. For example, you say "Base should start with Number" but your third row doesn't end up starting with a number? Also it's unclear how to determine the prefix from the base from the suffix - there's no logical way like length of characters to determine this

alexnajm

@MaheshR any more info?

MaheshR

Only if the length of part number is greater than = 10 we can create prefix, base and suffix. Base should not contain any alphabet and Suffix should not start with Number. I have done the manual working based on Length of Part Number. Like same way I need to do it in Altreyx. Kindly support. Let me know in case of any clarification.

caltang

I'm with @alexnajm on this. @MaheshR I still don't understand your ask... can you tell us what you are looking for exactly?

Perhaps give us a sample output of what you are looking to achieve? Also, what have you tried manually? We can see that and either replicate / automate via Alteryx.

alexnajm

@MaheshR we need a patterned, logical way to split your string into the three parts - otherwise we can assist no further

MaheshR

Please find the manual working sheet, I have attached details as requested.

Prefix Base Suffix.xlsx

alexnajm

There are still no consistent rules for anything under 10 digits, but for anything above 10 digits you can mimic your Excel formulas and use LEFT, SUBSTRING (same as MID), and RIGHT functions in Excel

MaheshR

Can you please help me with the formula for the length which is more than 10 in the workflow, as the formula is complex I am not able to get it right.