Find / Extract UK Postcode Using VBA

Hi All,

I have some code where I loop through and extract text from a series of PDF's. Within this are some addresses; however these contain postcodes for a number of addresses, which are enclosed by brackets. I want to clean this up and remove the brackets which surround these postcodes within my string. It would also be useful for other subsequent actions to be able to identify the postcodes within the string. The postcode is often surrounded by other text, in the middle of the string. Also, there could be other brackets included within the string around other text, which I do not want to remove (hence, simply locating and removing all brackets is not an option).

A UK Postcode is in one of the following six combinations of letters and numbers (and spaces): A1 1AA / A11 1AA / AA1 1AA / AA11 1AA / A1A 1AA / AA1A 1AA. I'm sure therefore that there must be a means of identifying and extracting this from a string. I thought perhaps the sequence to follow here would be to first check whether an alpha-numerical combination exists such as those referenced, and if so identify the start and end character location within that string. I could then extract that and also check whether the preceding and subsequent characters were brackets and if so remove these.

I have seen from searching online that there are formulas which can be used within a workbook for extracting a postcode. When testing however, these seem to be failing for me if the postcode isn't at the very end of the string within the cell.

One thing I did find (shared elsewhere in response to a similar query on another forum) was as below. I wasn't quite able to wrap my head around this however, therefore using and changing this to suit my specific requirements has been unsuccessful. This does seem to correctly identify whether a UK postcode exists within a cell; however this only returns a True or False statement within the cell with the UDF (i.e. rather than the location of the postcode within the cell). Also, given that my addresses are part of a string within VBA rather than within a cell, I imagine I could only make this work with the use of helper cells (i.e. by inserting the string into a cell, running the UDF in another cell, and then grabbing the output of that) - which if need be, then fine; however my preference would be to have this actioned within the VBA code without creating a UDF and running that from within a cell or cells (if that's possible).

Please Login or Register  to view this content.

If anyone has any pointers here, that would be greatly appreciated.

Cheers - Skins

You really have to include an anonymised sample of say 10-20 lines for someone to offer you a solution.

Questions
Are the brackets unique to search string, so could we search between them eg not other brackets appear in the string
What do you want your output to exactly look like
Do you need vba or are you open to a formula

Follow the yellow bar and attach a sample workbook, then someone will be better able to help you

Thanks davsth. I'm not too sure how much use the code will be, as I have yet to incorporate anything for the postcode identification. Also, this is just a separate code at this stage for the PDF information extract which I am working on before I move this into the master code for looping through all docs. Anyway, code as per the attached.

Essentially, AOPropName is the variable string which will contain the address and postcode.

In response to your queries - as mentioned in the original post, there can be other brackets within the string, so we can't use these to search against. The output would remain as per the existing string, just without the brackets surrounding the postcode. Also, I may wish to have a separate output just for the postcode extracted from the string. Also as mentioned, I would prefer VBA; however if this needed to be a formula, then I could make that work (i.e. by sending the string to a cell, running the formula in an adjacent cell, and then returning that cell value back to the VBA / assigning it as a variable etc.).

Thanks

Thanks Ali. This doesn't seem to search for the Postcode format however, therefore this would not catch other eventualities (i.e. something else within parentheses prior to the postcode). I would ideally like for this to specifically search for / identify a postcode (rather than searching for the brackets).

Hi Ali,

Appreciate you looking at this anyway. Just for info, there's no shifting goalposts here. It's the reason why I just set out what I was looking for, and didn't attach a workbook in the first instance (as I don't at this stage have a long list of representative data). I only attached the workbook after davsth suggested I did so. The original post made clear that I was looking to find the location of the postcode, not the parentheses.

Thanks all the same however, and appreciate you taking the time looking at this.

This UDF should pluck out the postcode from the original text assuming that there is one postcode present.

Please Login or Register  to view this content.

It's been 8 years already, so not sure the pattern is already outdated...
Format cell to recognised UK postcode

Please Login or Register  to view this content.

I've also managed to put together a formula which uses this and checks for and removes any parentheses from directly before or after the postcode where these exist

It might be easier to put a loop in the function to replace all occurrences of a postcode with brackets, with just the postcode. Then you can run the function as a UDF or as part of another procedure.

Please Login or Register  to view this content.