Regex

Question

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

Input data.xlsx

MarqueeCrew · Accepted Answer

Using a regex tool:

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

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

cheers,

mark

BretCarr · Accepted Answer

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

@Niwwa93

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

Tyro_abc · Accepted Answer

Spoiler

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