Format Cells for UK Postcode

Hi, I posted this thread recently but since then I've managed to solve part of what I need to achieve. So in order to not waste the experts' time, I've closed the original post and resubmitted it to reflect my current query.

I have a worksheet that is overwritten daily and contains address and details of customers who have purchased goods from our website in the previous 24hrs. The customer addresses are UK, Ireland and Europe.

I need to get the UK postcodes formatted to the put the space in the correct place.

There are many variations of the UK postcode and in addition the downloaded postcode data doesn't always have the space in the correct place, sometimes there's no space at all.

The correct format is that irrespective of the number of characters in the postcode, there is always one space 3 characters from the right. (eg AB12[space]3DL or L3[space]2RM)

I have written a macro using mouse clicks, but I realise this is probably very clunky, longwinded and bloated with code. In addition, it formats ALL postcodes in the worksheet, which is not what I need.

The worksheet contains varying numbers of rows each day, depending on number of sales, so the formatting will need to process all rows containing data, then stop when it has processed the last row.

The "Postcode" field cells are in Col J and the" Country Name" cells are in Col K.

This is basically what I would like to achieve:-
1) - If the cell in Col K = "United Kingdom", format cell J in the same row. Do this for all rows containing data.


The code I have currently is:-

Code:

Please Login or Register  to view this content.

Hope someone can help me resolve this!!!

One way, with a formula (assumes the value is in cell A2):

=LEFT(SUBSTITUTE(A2," ",""),LEN(SUBSTITUTE(SUBSTITUTE(A2," ","")," ",""))-3) & " " & RIGHT(SUBSTITUTE(A2," ",""),3)

It removes any spaces and then puts a space back in three characters from the right.


Regards

Hi,

I can't help thinking that a simple Excel formula would be the best option, even if this is used by a macro to populate a column of results. It's always, always better - and quicker to use standard Excel functions where possible rather than resorting to VBA.

Upload an example workbook containing all permutations of the post codes you're interested in and I'm sure a fairly simple string slicing function will be the answer.

Regards

Hi,

This should do as you wish

Please Login or Register  to view this content.

Where did all the replies come from!!

Try this in a standard module

Please Login or Register  to view this content.

This will "clean" any existing codes

If you then add this to the worksheet module

Please Login or Register  to view this content.

This will check codes as you go.

Basically this is a VBa answer to TMShucks' formula

Hope this helps

Put a check in for the length of the field. If it's less than 5, do something different to the solutions provided ... whatever that needs to be.

Regards

=IF(LEN(SUBSTITUTE(A2," ",""))>=5,LEFT(SUBSTITUTE(A2," ",""),LEN(SUBSTITUTE(A2," ",""))-3) & " " & RIGHT(SUBSTITUTE(A2," ",""),3),A2)


This will format anything greater than 4 characters and leave the others as it finds them. You can, of course, change that behaviour.


Regards

Hi Mike

In reply to your post #14

The first code goes into a standard module as you have correctly done

The second block of code goes in the sheet module for the sheet you want to control
Right-click on the sheet tab of the relevant sheet and select > View Code
Paste the second block of code in the resultant window.

You need both modules to make this work

See this example

1/. Select the Country from the Drop-down in column K and run see the change in column J

2/. Copy column A back into column J, this time select the country and run the macro "CheckPostCodes".

Hope this helps

P.S. I have slightly changed the function "FormatPostCode" to ensure that uppercase is returned

I've amended this workbook to use your sample data.

Follow the steps in Post #19

The London codes don't work properlly, what do you need with them?

=UPPER(IF(B2="United Kingdom",IF(LEN(SUBSTITUTE(A2," ",""))>=5,LEFT(SUBSTITUTE(A2," ",""),LEN(SUBSTITUTE(A2," ",""))-3) & " " & RIGHT(SUBSTITUTE(A2," ",""),3),A2),"")


Regards

@StephenR: Thanks ... it seems to keep growing and I was beginning to lose track! :-) Hope this solves the problem! Regards

The code for change as you go must be in a worksheet module, because it is triggered by one of the relevant cells being changed.
However if you just want to clean the codes en-masse then you don't need that code.

Try this in your "Personal.xls"

Please Login or Register  to view this content.

@Marcol:

I think you need to check the length of the "postcode" after you have trimmed it and substituted for the space(s). Otherwise, a valid Manchester central postcode, for example M5 3BE (or m53be) would be passed over. But I might have missed something ;-)

Regards

My apologies ... I must have missed the last bracket when I copied and pasted the formula. In this instance, Excel made a good stab at correcting it for me ;-)

Are you saying that you want to "review" each post code in column A and update it, if necessary?

Has anyone else offered such a solution? I'm always kind of wary of overwriting the source data ... at least, initially ... just in case it screws up along the way.

That said' I'll have a look at it and get back to you.

Regards

OK, see if this is what you want ... please don't test it on your live data ;-)

Please Login or Register  to view this content.

Regards

You are correct TM it also applies to

BIRMINGHAM, LONDON, GLASGOW, LIVERPOOL, MANCHESTER, SHEFFIELD


All of which have 1 letter & 1 digit codes

It gets a bit more interesting as it grows

Please Login or Register  to view this content.

This needs refining , and a little more error checking built into the function to make it bullet proof, but got to go for a while.

Give the attached a try.

Hi Mike, you're welcome. Thanks for the feedback and the rep.

I tried it with a variety of rubbish, so I think it will cope ... provided there are no other anomalies :-)

Regards

This is a four year old thread.

You would be better starting your own thread with your own specific requirements. Plus, it's considered hijacking

You can always link back to this thread for background but it sounds as though your requirement is slightly different.

I suspect that someone could use a Regular Expression to check the format ... unfortunately, not me.

Regards, TMS