I am currently migrating data from Goldmine to Salesforce platform.
Unfortunately Client stored street name, postal code and sometimes city name in one line.

Zweedsestraat 24 7418 BG
Zutphen-Emmerikseweg 5, 7223 DA Baak	

So you can not use simple rule like splinting by comma because often there is no coma or RIGHT function to copy last 7 characters because in last position can be city name.

What to do then?
We need to use regular expressions and LibreOffice.
You will ask: why LibreOffice? Answer is simple it's smarter than Excel because it can understand RegExp in formulas. With Excel you need to use macros, see this link.

Let's go!
First you need to enable RegExp support in LibreOffice Calc. Go to Tools -> Options -> LibreOffice Calc -> Calculate and select "Enable Regular Expressions in Formulas".
Without this function enabled you will get error #VALUE! if you will try to use RegExp in formula.

Then we need to write own RegExp or find good example here regexlib.com like I did.
Additionally we can use simple tool like The Regex Coach to validate our rule.

In my case  correct RegExp will be "[1-9]


[0-9]{3} ?[a-zA-Z]{2}" without ^ and $.
You can read about meaning of these symbols here.

Step 1: Find where in the string postal code begins.

=SEARCH("[1-9]{1}[0-9]{3} ?[a-z]{2}";P1)

Step 2: How many characters from beginning we need? Lets look at

7223 DA

it's 7 characters including space.

Step 3. We are ready! Now we need formula that will copy only 7 characters from point where postal code begins.

=MID(P1;(SEARCH("[1-9]{1}[0-9]{3} ?[a-z]{2}";P1));7)


If there is no postal code then error message  #VALUE! is displayed.

© Paweł Woźniak