Regex

[Niwwa93]

Hello everyone,

I am having trouble with a Regex function and I hope one of you can help.

Attached is a list of Dutch street names + numbers + additions (all in 1 column). My goals is to split this column into two columns: 1) street name and 2) street number and addition (if addition is applicable).

Additions occur is various forms, for example:

1) Burgemeester van Oostenweg 1 L  -> Addition is "L"

2) Nieuwe Herengracht 11 -D -> Addition is "-D"

3) Bloklaan 22 A-sz04 -> Addition is "A-sz01"

4) Bartholomeus Diazstraat 20 -III -> Addition is "-III"

I used the following Regex function: \A(.*?)\s+(\d+[a-zA-Z]{0,1}\s{0,1}[-]{1}\s{0,1}\d*[a-zA-Z]{0,1}|\d+[a-zA-Z-]{0,1}\d*[a-zA-Z]{0,1})

This Regex incorrectly removes some of the additions. For example the output for Burgemeester van Oostenweg 1 L is "Burgemeester van Oostenweg" and "1" instead of "Burgerrmeester van Oostenweg" and "1 L".

What should I adjust in my regex so revolve this?

Many thanks in advance for your help.

Regards,

Chris

[MarqueeCrew · Accepted solution]

Using a regex tool:

(.*?)\s(\d+).*

set it to parse and you'll get your 2 fields

 cheers,

 mark

[BretCarr · Accepted solution]

@MarqueeCrew had everything there but I feel you want to capture both the number and the addition that follows it (when applicable), no?

Move the parentheses in the pattern to the end and it works for me when set to parse.

^(.*?)\s(\d+.*)

Hope that helps!

[Qiu · Accepted solution]

@Niwwa93 

Similar with others but provide a sample workflow and data output.

[Tyro_abc · Accepted solution]

Spoiler

Spoiler

Formula of Parsing: (\w+)\s(\d.*)

[Niwwa93]

@MarqueeCrew @BretCarr @Qiu @arundhuti726

Thanks for your solutions and quick replies!