Skip to content

Cookie settings

Choose which kinds of cookies you allow. You can change your choice at any time via Cookie settings at the bottom of every page. Privacy statement

Necessary

Always on

Needed to log in, keep your cart and remember your choices. These are always on.

Which cookies?
  • weboke_sessie, weboke_klant, weboke_remember: your session and staying logged in
  • weboke_basket: your cart
  • wkBtwIncl: showing prices with or without VAT
  • wok_cookies: your cookie choice, 12 months

Measures which ad leads to a visit or order, so we do not pay for ads that do nothing.

Which cookies?
  • Google Ads via Google Tag Manager (_gcl_au, _gcl_aw), up to 90 days
  • OpenAI Ads, ads in ChatGPT
  • wok_aff: which partner referred you to us (affiliate programme), 60 days

4.6

Country and language

Each country has its own site, with prices and VAT for that country.

View

Extracting street, house number and suffix from a single address column

Addresses from a web shop, membership records or an old customer file are often in a single column: "Dorpsstraat 12-2". For shipping labels, a mailing or an import for a courier, you need the street, house number and suffix separately. Text to Columns doesn't handle this well, because street names contain spaces themselves. Splitting first and last names is covered in Splitting first name and surname from one column; addresses are trickier. Euro-Office has the functions REGEXEXTRACT and REGEXREPLACE for this, which work with a search pattern.

As an example, we'll use the customer list of Bloemisterij Het Zonnetje, with the following in column A:

  • Koningin Julianalaan 15 a
  • Dorpsstraat 12-2
  • Laan 1940-1945 8B
  • Kerkplein 7
  • Hoofdstraat 120 bis

Step 1: the house number with suffix

Type in B2:

=REGEXEXTRACT(A2,"\d+\S*( ?[A-Za-z]{1,3})?$")

The pattern between the quotation marks means: one or more digits (\d+), then optionally characters without a space (\S*, such as -2 or B), then optionally a space with 1 to 3 letters (such as " a" or " bis"), all at the very end of the text ($). Result: 15 a, 12-2, 8B, 7 and 120 bis. The years in "Laan 1940-1945" are left alone, because only the last part of the address counts.

Step 2: the street

Type in C2:

=TRIM(REGEXREPLACE(A2,"\d+\S*( ?[A-Za-z]{1,3})?$",""))

REGEXREPLACE replaces the same part with nothing, and TRIM removes the space at the end. You're left with Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 and so on.

Step 3: number and suffix separately

If you also need the number and the suffix separately, continue with the result of step 1 in B2:

  • Just the number, in D2: =REGEXEXTRACT(B2,"^\d+") (the digits at the start): 15, 12, 8, 7, 120.
  • The suffix, in E2: =TRIM(REGEXREPLACE(B2,"^\d+[- ]?","")): a, 2, B, nothing, bis.

Then fill the formulas down to the last row. Or work column by column: select B2:B6, for example, type the formula and press Ctrl+Enter; it's then entered in all selected cells in one go.

Addresses split into street, number and suffix
House number with suffix (1), street (2), number (3) and suffix (4), each with a single formula

An address with commas

If the whole address is in one cell, such as "Dorpsstraat 12, 1234 AB Utrecht", =TEXTSPLIT(A9,", ") splits it at the comma: "Dorpsstraat 12" goes in the cell itself and "1234 AB Utrecht" in the cell next to it. You can then separate the postcode and town with TEXTBEFORE and TEXTAFTER, which take the text before or after the nth delimiter. If "1234 AB Utrecht" is in C9:

  • Postcode: =TEXTBEFORE(C9," ",2) returns 1234 AB (everything before the second space).
  • Town: =TEXTAFTER(C9," ",2) returns Utrecht.

Done? Turn them into fixed values

The split columns are formulas that refer to column A. If you want to delete column A, or send the file to someone with an older version of Excel, convert the results to plain text first: select the columns, copy them with Ctrl+C and paste them in the same place with Ctrl+V. A small (Ctrl) button appears below the pasted cells. Click it and, under Value, choose Paste only value.

The paste options after pasting
The (Ctrl) button below the pasted cells, with the Paste only value option under Value

Pitfalls

  • Exceptions will remain: "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Apply a filter to the columns and look for empty or odd results before you use the list.
  • Capital letters: the pattern [A-Za-z] includes both lowercase and capital letters. If you use only [a-z], 8B will be missed.
  • #N/A from REGEXEXTRACT means the pattern wasn't found, for example an address without a house number.
  • Other spreadsheets: Excel only supports these REGEX functions in recent versions of Microsoft 365. A colleague with an older version will see #NAME?. In that case, convert the results to values first.
  • You can also do it without REGEX, but with more limitations: =TEXTAFTER(A2," ",-1) returns everything after the last space. That works for "Kerkplein 7" and "Dorpsstraat 12-2", but with "15 a" you only get the a.

One moment…

Set up your domains

For each domain, arrange the nameservers, the link to your hosting and who becomes the owner.

Loading your domains…