Split column before X numbers (as text) in cell

[JMusch]

Hey all,

Couldn't find a solution to this, hopefully somebody can help me out with the following problem.
I have a list of data, of which one column is a bit messy.
This column contains a lot of information as text; Company name, Address, Postal Code & Place.
More specifically, the format in cells of these column is as follows (without the [] brackets):

[Company name]#[Street name] [Housenumber][PostalCodeNumber] [PostalCodeLetters] [Place]

An example of 3 cells in this column:

Pharma A#Main street 1230000 ZZ New York
Semicon 1#Cowstreet 551234 AB Tokyo
Bakery Z#Sheepstreet 44039999 SS Amsterdam

I want to isolate the postal codes and put the number part of it in a separate column, so I can filter geographically.
The important part here is that the postal codes are all Dutch, all Dutch postal codes always have the following format: exactly 4 number + exactly 2 capital letters
From the example above, the postal codes are therefore 0000 ZZ, 1234 AB & 9999 SS.
So the goal is to get a new column including only the postal numbers (preferably without letters):

0000
1234
9999

The problem here of course, is that the housenumbers are right next to the numbers of the postal code without any space or other symbol as delimiter. The housenumbers in the example are 123, 55 & 4403.
Since the column contains over 6000 cells, this is not doable manually.
So we need to devise a function that somehow detects the last 4 numbers in the cell and splits it (or copies into a new column).
I have been trying some, but can't work it out. Hopefully one of you can help!

Thanks in advance!


Edit 1: added example file, should clarify a lot

Edit 2: I can manage to split the company name from the rest of the text using the # symbol as delimiter, problem is getting the rest done.

Edit 3: Solved:

Thanks for the help everyone!

Solution 1 (oedelre) & 3 (sandy666) have a little problem that text to column by fixed width doesn't work. In my example the width of the string until the numbers were accidentally the same (I didn't notice or clarify), but in reality these strings can be extremely long or short. Otherwise they would work perfectly.
Solution 2 (Tony Valko) has the problem that, because these text strings weren't always of equal length, it sometimes gave a string of text instead of the postal code back.
So what I did was create 10 different functions (and columns) where I altered the function as such: =LEFT(MID(A3,SEARCH("X??? ?? ",A3),7),4) where X=0-9. Then I simply combined it back into one column.

Not a universal solution to different problems like mine, but for me it works.
Thanks again!