Find Replace

[kjhanwar]

Hi,

Invoice number contains Financial year information like 123/18-19. I want to remove /18-19 and only want 123 as output. There are two question

1) If I have all permutation combination of financial year data in invoice number like /18-19, /2018-19, 18-19, 201819 etc. How to remove these financial year numbers from invoice number so that invoice will contain only alpha-bates or number apart from these /18-19, /2018-19, 18-19, 201819 etc.

2) if I don't have permutation and combination of financial year information in invoice number, how to remove financial year information in invoice number whenever invoice contains financial year data also.

Example

Input	Required output
123	123
abc/123	abc/123
123/2018-19	123
124/2018/19	124
125/18-19	125
abd-18-19	abd

Basically i want to do the following find replace:-

Find	Replace
/2018-19
/2018/19
/18-19
-18-19

I have tried with find and replace tools but it was replacing only alpha-bates and not numbers.

Please suggest appropriate tool.

Thanks in Advance

[JosephSerpis]

Hi @kjhanwar  I would suggest to use the regex tool for this suggestion to parse the invoice numbers and ignoring the financial year information. I've mocked up an example for simplicity of the the regex code I replaced (-) with / so you can see in the regex code i'm looking for everything before / . Hope this helps.

[AkisM]

Use the regex tool as JS420 said above, but instead of the expression he used, try using this:

[\/\-](\d\d\-|\d{4}).+

Choose "replace" as output method. On replacement string, leave it empty to simply delete the unwanted characters.

[kjhanwar]

Thanks for your quick response.

I have used the method suggested by you. This method is not working where my input is STE/18-19/74 (for example), in this case Alteryx is giving me STE as output but i require invoice no as "74" in my output. How to handle these kind of challenges?

Some example are mentioned below along with required output and Alteryx output.

Before	Output coming in Alteryx	Required Output
123/18-19	123	123
/18-19	[Null]	[Null]
/2018-19	[Null]	[Null]
aa	aa	aa
/2018/19	[Null]	[Null]
abc/123/18-19	abc	abc
STE/18-19/74	STE	74
2018-2019/017	2018	17
18-19/PC/108	18	108
B2B/000000000666	B2B	666
BE/03/18-19	BE	3
AB/1077/18-19	AB	1077
GCR/18-19/555	GCR	555
SMP/18-19/468	SMP	468
FS-188/2018-19	FS	FS-188

Thanks in advance

[kjhanwar]

Hi,

I tried this but it is not giving me desired output. Basically from invoice number i want to remove all special character, alpha-bates and financial year combination so that invoice number contains only number. 

With only number in invoice number, I can use concatenate and link it with vendor name and match it with my data.

Thanks

[nkumarjha · Accepted solution]

Hi @kjhanwar 

Use Data Cleansing to Remove Letters and Punctuation use formula tool and use contain and replace formula to achieve the  result. WF attached,