Split column before X numbers (as text) in cell

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!

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

first text to column.

After that:

d9 =right(LOOKUP(99^99,--("0"&MID(c9,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},c9&"0123456789")),ROW($1:$10000)))),4) and drag down

See the attached file.

Not sure which ones you still need...

For Main Goal Column: Postal Number...

=LEFT(MID(A3,SEARCH("???? ?? ",A3),7),4)

For Extra goal 3 column: Postal Code...

=MID(A3,SEARCH("???? ?? ",A3),7)

For Extra goal 4 column: Place...

=MID(A3,SEARCH("???? ?? ",A3)+8,20)

Text to columns - Fixed width - select first vertical arrow right before first number, select second vertical arrow right after last number, select unnecessary columns to (skip), finish. You will get (e.g. in B1:B3):
1230000
551234 AB
44039999
and now

Please Login or Register  to view this content.

and drag down