RegEx Help to Split into columns

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

Parse

Accepted answers

philipmannering

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

All comments

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.

M.

@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?

philipmannering

Not perfect, but a good start...

Parse Address.yxmd

@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

Quick Links

Tags

This months top contributors

Hollingsworth 335