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

Regex: Extracting a specific number between text and numbers as well

Cfdiaz2103
8 - Asteroid

Hello team,

 

Currently I'm working on a workflow that should be able to identify and split to columns an invoice number among tons on information (Text and numbers). I know that it could be done with a Regex Tool, but I have not been able to identify a pattern to tokenize this specific data.

 

Here, I'm sharing with you a sample of the current data and the desired output. Note that the invoice number I would like to split is colored in red.

 

I'd appreciate any kind of support you can give me.

 

Best wishes.

6 REPLIES 6
IraWatt
17 - Castor
17 - Castor

Hey @Cfdiaz2103,

I don't know if what logic you would apply here:

"ANDRES RODRIGUEZ FORERO FC 1777, ARRIENDO OFICINA 31 Y PARQUEADERO MARZO 2022;"

How can we tell 2022 is not an invoice number?

Cfdiaz2103
8 - Asteroid

Hello @IraWatt!

 

Well, I do believe that the invoice number is preceded by a prefix such as FAC, FC, LIQ or . If it's possible to extract first the invoice number alongside its corresponding prefix, and then by trimming the prefix, it would be great, but I've got no idea how I could do that.

 

Thanks!

DataNath
17 - Castor

@Cfdiaz2103 if that's an extensive list of prefixes, then this will work. It's also dynamic so it doesn't matter how many invoice numbers are in the column as, if you use the tokenize method and split to columns, you need to specify to how many. If this number can vary then you'll end up losing data (e.g. you have your tokenize set to 4 columns and there's 9 invoice numbers in a field, it would drop all after the 4th).

 

DataNath_0-1656079891941.png

 

If you have more possible prefixes, just add them to the (?:FAC|FC|LIQ|N°) part of the expression in the RegEx with a '|' before them. For example, if you needed to add 2 more prefixes of ABC and XYZ, this would become (?:FAC|FC|LIQ|N°|ABC|XYZ):

 

DataNath_1-1656079919331.png

 

HomesickSurfer
12 - Quasar

Hi @Cfdiaz2103 

 

Here you go.  Workflow attached.  This will allow for many invoices...and with the Invoice1, Invoice 2, etc. field names

 

HomesickSurfer_0-1656081393832.png

 

Cfdiaz2103
8 - Asteroid

Hi @DataNath !

 

Absolutely! That solution works perfectly. Great work!

 

I really appreciate that.

 

Thanks!

 

 

Cfdiaz2103
8 - Asteroid

Hi @HomesickSurfer 

 

Fantastic! It works great.

 

I really appreciate that.

 

Thanks! 😀

Labels