UK Postcode Issue - vba needed

[Sailaja A]

Hello Forum,

I am having an issue with UK Postcode copying.
The issue is: I have address column and postcode column. Now, I am supposed find the postcode from address cell and copy it to its corresponding postcode column. The postcode, can be found in following way:
a) Opening brace for Postcode in Address is missing
b) Closing brace for Postcode in Address is missing
c) Both a and b,
d) Postcode is completely missing in Address, then no issue
e) More than one Post code exist in Address, then copy first postcode

I am able to copy the postcode from address to postcode column, if it is with in braces. But for all the other conditions, I fail. Please help me. Below here you can find different types of data with above mentioned conditions, where I am failing to copy the postcode from address to postcode column:

Address	Postcode
25 Odette Duval House, Stepney Way, London, IE1 3HJ)	IE1 3HJ
11 Peel Street, Denton, Manchester (M34 3JY	M34 3JY
8 Leeward Court, Quay 430, Kennet Street, Wapping B15 3JF and parking space 185	B15 3JF
Flat 42, Canal Wharf 305, Kingsland Road, London 2 Boscobel Road, Great Lever, Bolton, (BL3 2LN) and 184 Green Lane, Great Lever, Bolton, (BL3 2LF)	BL3 2LN

Please go through the vba for this purpose I am using:

Please Login or Register  to view this content.

I have valid post code formats of UK.

FORMAT	EXAMPLE
AN NAA	M1 1AA
ANN NAA	M60 1NW
AAN NAA	CR2 6XH
AANN NAA	DN55 1PT
ANA NAA	W1A 1HQ
AANA NAA	EC1A 1BB

Please note the following:
- The letters Q, V and X are not used in the first position
- The letters I,J and Z are not used in the second position.
- The only letters to appear in the third position are A, B, C, D, E, F, G, H, J, K, S, T, U and W.
- The second half of the postcode is always consistent numeric, alpha, alpha format and the letters C, I, K, M, O and
V are never used.
Postcodes should always be in BLOCK CAPITALS. Leave a clear space of one character between the two parts of the postcode and do not join the
characters in any way.

[Kyle123]

Have a look here http://www.mrexcel.com/forum/excel-q...ost-codes.html

[Richard Buttrey]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Sailaja A]

Hello Richard Buttrey,

Sorry for not following forum rules. I am new here and much aware of the system. Thanks for letting me know the procedure.

I am going through the links forwarded where I can find the rules and regulations for forum postings.

Regards,
Sailaja

[Sailaja A]

Hi Kyle123,

I followed the link you have provided and ran the code available there. But, it throws an "Invalid Name Error"

[Richard Buttrey]

Hi,

The following simple although not pretty function will give you the post code excluding the () parentheses.

[FORMULA]
=RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),")",""),LEN(SUBSTITUTE(SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),")",""))-FIND("|",SUBSTITUTE(SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),")",""))-1+IF(ISERROR(FIND("(",SUBSTITUTE(SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),")",""))),1,0))

It won't pick up stuff like the 3rd item where there are no () which can be used to identify the code part of the string, but maybe you can work around that if they are relatively few in number.

[Sailaja A]

Hello Richard,

This formula throws an "Error in Value".
I have hundreds of rows to work on. So is it possible to get a vba macro where it takes of everything just by running the macro.

Link given by Kyle123 (http://www.mrexcel.com/forum/excel-q...ost-codes.html) discusses on addresses where PostCode does not fell in a "()".

Could you please look into my Code and let me know where I am facing issue.

Thanks in Advance,
Sailaja

[Richard Buttrey]

Hello Richard,

This formula throws an "Error in Value".
I have hundreds of rows to work on. So is it possible to get a vba macro where it takes of everything just by running the macro.

Sailaja

Please upload your workbook with a few of the rows that are giving you an error value with the formula I gave you. I was trying to give you a VBE free option and the compound formula I gave you stripped out the post code for the few sample rows you originally showed.

[Sailaja A]

Sure Richard.
UK PostCode Issue.xlsx

Please go through the ExcelSheet. I collected some rows with possible conditions and placed them in this excel sheet. I feel somewhere I am missing something. I do not know what. Never struggled for a code like this before.

Column A consists of Address and Column B PostCode. Now, a valid Postcode be copied from Address to corresponding PostCode cell.

Thanks in Advance.

Regards
Sailaja

[Richard Buttrey]

Hi,

You posted the workbook but have not added the formula I gave you and as I requested.
If you had you would have found it does exactly what you want with the exception of the A1 cell in which (uniquely) it has a lot of trailing spaces. If you eliminated those (try =Trim(A1) ) or do it manually you'll find that too extracts the post code.

[AB33]

As Kyle's suggested, the code written by Rick seems to work. If you type in C1==UKPostCode(A1) and drag it down, you get the result. I am not sure though if the code works with 2 post codes in a single row. For e.g., in row 5, you have 2 different post codes. The code returns the first post code, BL3 2LN, but there is another post code on the same row,BL3 2LF

[TMS]

@AB33: agreed, the code works well. Note:

e) More than one Post code exist in Address, then copy first postcode

Regards, TMS

[AB33]

Hi Trevor,
Thanks for the pointer!
I have to admit I did not read any of the OP's request, I just jumped in to the link and tried Rick's code.

[TMS]

Workbook with sample data, formula and function.

Regards, TMS

[Sailaja A]

Hello Richard,
Thank you very much for the help.

And Sorry that I missed uploading the file with formula as requested. One more thanks for identifying the non-printables also.

Note : I have a separate function which takes care of trimming extra spaces and non-printables which works very well.

Regards,
Sailaja

[Sailaja A]

Thanks a lot TMS.

Thanks for showing the formula and function in different columns. I was about to upload a similar file.

The mistake from my side for Function is : I missed declaration of Option Explicit . But still I am getting "Invalid Name error". Please go through the xlsm file.Post code with function and resultant column.xlsm

Richard, please note that the column result given by TMS is the same result, I am also getting after running the formula.

Regards,
Sailaja

[TMS]

Thanks for the rep


Nothing to do with Option Explicit. That just ensures that all variables as declared.

The issue is that you need to put the code in a standard module, not a worksheet or workbook class module.

See the attachment.


Regards, TMS

[Sailaja A]

Great help for a new kid like me on the blog !
Thank you very much TMS ! And Hats off to the actual coding person Rick Rothstein

Now I am with no error. Thank you All for your help and time.

Regards,
Sailaja

[TMS]

You're welcome.

[Sailaja A]

I am back again. With a twist in handling the above mentioned solution.

My issue is: I test sampled the UKPostCode Function on a very less number of rows. Now, I have a very very huge data to handle (some where 10k rows). When I try copying the processed data to another file, I am having issue with "Invalid Name Error" again.

Could you please help me with a macro which copies PostCode from Column A to Column B.

[Sailaja A]

I am back again. With a twist in handling the above mentioned solution.

My issue is: I test sampled the UKPostCode Function on a very less number of rows. Now, I have a very very huge data to handle (some where 10k rows). When I try copying the processed data to another file, I am having issue with "Invalid Name Error" again. I tried changing name of the Module name from UKPostCode to "UKPostCodes" as suggested by Mr.Snakehips at http://www.mrexcel.com/forum/excel-q...t-codes-3.html also.

Could you please help me with a macro which copies PostCode from Column A to Column B.

[TMS]

If you are copying the column with the formula in it to another workbook, the module and VBA subroutine will not be present in the other workbook ... unless you have copied it across too. That would result in a #NAME error.

If the module and subroutine are present in the second workbook, have you enabled macros?

If you only want a list of post codes, use Paste Special | Values.

Regards, TMS

[Sailaja A]

You are Absolutely correct TMShucks. I tried paste special -> Values. It Works.
But, out of my enthusiasm, I am trying to write a macro which copies valid Post code from Address (Column A) to its corresponding PostCode cell in Column B.
Please help me with my code.

Please Login or Register  to view this content.

[TMS]

Your original question has been answered. This thread should be marked as solved.

And the rider about copying to another workbook has also been answered.

You are asking a new question, albeit related, and it should be asked in a new thread.

Regards, TMS

[Sailaja A]

OK TMShucks. Thanks for suggesting me.