Extracting UK post codes

[Derek Stephen]

Hi

How can I extract UK post codes from a non uniform text string into a separate row

Derek

 

[Snakehips]

When you enter =U into a cell, do you get a function drop down appear showing UKPostCode and UPPER?

 

[Snakehips]

Just another thought. You don't have 'Macros disabled without notification' do you?

 

[Derek Stephen]

Hi Tony

When I enter the formula =U in he dropdown I get UKPostCode( I do no get "upper" I assume this is because I have only put in the first part of Tony's code. I thought It would be better to get the first part working.

I have save the worksheet as macro enabled but when I open it I do not get the message to enable it.

Derek

 

[baldmosher]

One thing to add, if you have a list of all the relevant post code prefixes, you can just do a pattern match for those using Instr in VBA -- very slow, but very effective. It's unlikely that "TN15" or "WC2V" would appear elsewhere in a long string so you could assume it's a post code and just extract the first 8 chars after that, and then look at cleaning the ends of those up afterwards (i.e. delete everything >3 chars after the central space, where you have a central space of course)

Seems I can't attach files, but I do have a list of all the valid prefixes, so if you want it just PM me with your email address

 

[Snakehips]

Because you speak of saving MacroEnabled, I assume that you are using 2007 or later?
Goto Excel Options - Trust Center - Trust Center Settings - Macro Settings and make sure that you do NOT have 'Disable all Macros without notification' ticked.

If you do then change it to 'Disable all Macros with notification'.

Then close and reopen the file and Enable Macros when asked.

 

[Snakehips]

For completeness........

I have had the opportunity to look at Derek's file and the issue was that Derek had named the code module as UKPostCode , exactly the same name as Rick's UDF.
This obviously creates some conflict within Excel and thus the UDF would not function.

When the module name was changed to UKPostCodes Rick's first UDF extracted the post codes perfectly.

 

[Derek Stephen]

Thanks to everyone who helped me on this problem , especially Rick an Tony

Derek