Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Regex Query (Alphabets and numbers at particukar locations)

7 - Meteor

I want the output to be in such that ENTY should be key phrase and next three digits can be either alphabets(P00 or RVT) or numbers and next 5 will be numbers (00078) and last three will be again alphabets (RTE). Also, if there is any space after ENTY, I want the regex to ignore that space.

 

Text field :

1. ENTYP00031 ID:ENTYP0003178DST 

2. ENTYR00431 ID:ENTY RVT00078RTE

 

Output needed

1. ENTYP0003178DST

2. ENTYRV000078RTE

 

Highlighted
Alteryx
Alteryx

Hi @Pranee_007 

 

Have you tried just doing text to columns on the colon?

 

The RegEx to parse what you asked for is:

 

ENTY\s{0,1}[P0RVT]{3}[0-9]{5}[A-Z]{3}

 

This can be written and tested in https://regex101.com/

 

You'll have to clear the space out with the Data Cleansing tools.

 

Luke

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @Pranee_007 

 

Would this work well?

 

REGEX_Replace([Field1], ".*(ENTY)\s*(\w{3}\d{5}[A-Z]{3}).*", "$1$2")

 

Cheers,

Highlighted
Alteryx Certified Partner

Hi @Pranee_007 
I did it in two steps. First remove the spaces

OllieClarke_0-1576246890433.png

Next parse the string:

OllieClarke_1-1576246910058.png

The regex for step 2 is as follows:

(ENTY\w{3}\d{5}[a-z]{3})

Hope that helps,

 

Ollie

Labels