topic Re: RegEx Help to Split into columns in Alteryx Designer Desktop Discussions

RegEx Help to Split into columns

messi007 — Thu, 12 Nov 2020 19:12:20 GMT

Dear Community,

As I'm not really good with RegEx I request your help me in my use case below:

I have a filed like this and I want to split it on columns

12 street A abc 12345 city abc@gmail.com 12 34 56 78 91

7 street 12345 city abc 12 34 56 78 91 NO MAIL

ABC street 13 abc AB12CD LONDON ROYAUME UNI mail@mail.com (+44)7778886662

12 street A abc 12345 city abc@gmail.com 12 34 56 78 91 / 21 54 62 79 10

When I put street that's mean several letter and digit as well

Below what I expect

adress	Place	PostalCode	Localite	Residence	Telephone1	Telephone2	AdresseMail
12 street A abc		12345	city		12 34 56 78 91		abc@gmail.com
7 street		12345	city abc		12 34 56 78 91
ABC street 13 abc		AB12CD	LONDON		(+44)7778886662		mail@mail.com
12 street A abc		12345	city		12 34 56 78 91	21 54 62 79 10	abc@gmail.com

I really appreciate your help 🙂

Regards

Re: RegEx Help to Split into columns

mceleavey — Thu, 12 Nov 2020 21:38:59 GMT

Hi @messi007 ,

I don't see how this is possible as you need to impose rules to automate regex parsing, and there doesn't appear to be any, unless I'm missing something.

For example, how are you differentiating between the first line, which uses "12 Street A abc", and the second line which just takes "7 Street"?

I don't see any way you can do this without consistent rules, sorry.

Re: RegEx Help to Split into columns

Qiu — Thu, 12 Nov 2020 22:49:09 GMT

@messi007
Agree with @mceleavey , I can NOT see pattern here, therefore RegEx is no feasible.

Perhaps Excel Text to Column migh do a bit help?

Re: RegEx Help to Split into columns

philipmannering — Fri, 13 Nov 2020 02:48:14 GMT

Not perfect, but a good start...

Re: RegEx Help to Split into columns

messi007 — Fri, 13 Nov 2020 08:14:08 GMT

@philipmannering Thanks for the answer it's really a good way to extract the e-mail and the phone number it's working perfectly 🙂

Howerver for the address, postal code and city it's not working 😞 .

Because it's my fault in the example I shared as we don't have the word street in address we have the address like below.

12 alteryx appart A abc 12345 city abc@gmail.com 12 34 56 78 91

7 street 12345 city abc 12 34 56 78 91 NO MAIL

ABC bat 13 abc AB12CD LONDON ROYAUME UNI mail@mail.com (+44)7778886662

12 rue A abc 12345 city abc@gmail.com 12 34 56 78 91 / 21 54 62 79 10

I tried with regex to get those three column as I have seen the most of the case I have

Adress 5 degit postal code city and the phone number or e-mail.

abc 14 Bat 2 Residence, 14 abc abc, 12345 city,

vry 2 18 xyz xyz 06789 london 12 34 45 54 54 mail@mail

xyz abc 04562 denver lololala@gmail.com 12 34 45 54 54

Below the regex I have done may be you can help me improve it

(.*)(\d{5})(.*)\s(.*@.*)

I'm stuck how to get the first part until I found e-mail or phone number.

Get the first part until a found 5 degit of the postal code...

I really appreciate your help 🙂

Regards

Re: RegEx Help to Split into columns

philipmannering — Fri, 13 Nov 2020 09:34:55 GMT

Not sure. You could try something like this (but it's very ugly),

(.*?) \b(\d{5}|\w{1,2}\d{1,2}\s?\d\w{1,2})\b \b(\w+)\b

Re: RegEx Help to Split into columns

messi007 — Fri, 13 Nov 2020 09:57:37 GMT

Dear @philipmannering,

thank you for you help you're really a master of Regex 🙂

It's working but if have a problem

12 alteryx appart A abc 12345 city abc abc@gmail.com 12 34 56 78 91

it give me the address , the postal code and only the first part of the city

12 alteryx appart A abc

12345

city

I need this

12 alteryx appart A abc

12345

city abc

Thanks again 🙂

Regards