Format Cells for UK Postcode

[MikeWaring]

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:

Sub PostCode_Format()
'

    Columns("J:J").Select
    Selection.Copy
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Range("K2").Select
    

    ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!REVERSETEXT(SUBSTITUTE(RC[-1],"" "",""""))"
    Selection.AutoFill Destination:=Range("K2:K400"), Type:=xlFillDefault
    Range("K2").Select
    
    Columns("K:K").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    

    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=UPPER(PERSONAL.XLS!REVERSETEXT(REPLACE(RC[1],3,1,MID(RC[1],3,1)&"" "")))"
    

    Selection.AutoFill Destination:=Range("J2:J400"), Type:=xlFillDefault
    Range("J2:J400").Select
    Columns("J:J").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
End Sub

Hope someone can help me resolve this!!!

[TMS]

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

[StephenR]

Are some of the postcodes correctly formatted already?

[MikeWaring]

Hi Stephen, thanks for the quick response.
Yes, some are formatted correctly, but by no means all of them. In addition, I need it to ignore any that are non UK postcodes, hence the conditioaln lookup to Col K
Rgds
mike

[Richard Buttrey]

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

[MikeWaring]

Hi Richard, yes I agree, but I scoured various forums and books but I couldn't find anything that seemed suitable. However, this is my own layman's opinion and would welcome anything that works, especially given the extra complications of the old London postcodes that I've just tested (see my post of 2:21pm today).

[sweep]

Hi,

This should do as you wish

Sub postcodes()
Dim rCell As Range
For Each rCell In Range("K:K")
If rCell = "United Kingdom" And Not rCell.Offset(0, -1) = "" Then

rCell.Offset(0, -1).Formula = "=LEFT(SUBSTITUTE(UPPER(""" & rCell.Offset(0, -1).Value & """),"" "",""""),LEN(SUBSTITUTE(UPPER(""" & rCell.Offset(0, -1).Value & """),"" "",""""))-3)&"" ""&RIGHT(SUBSTITUTE(UPPER(""" & rCell.Offset(0, -1).Value & """),"" "",""""),3)"
rCell.Offset(0, -1).Value = rCell.Offset(0, -1).Value
End If

Next
End Sub

[StephenR]

This perhaps, if some are already correct:

Sub x()

Dim r As Long

For r = 1 To Cells(Rows.Count, "J").End(xlUp).Row
    If Cells(r, "K") = "United Kingdom" Then
        With CreateObject("vbscript.regexp")
            .Global = True
            .Pattern = "[A-Z]{1,2}[0-9]{1,2}\s[0-9][A-Z]{2}"
            If Not .test(Cells(r, "J")) Then
                Cells(r, "J") = Left(Cells(r, "J"), Len(Cells(r, "J")) - 3) & " " & Right(Cells(r, "j"), 3)
            End If
       End With
    End If
Next r

End Sub

[MikeWaring]

Hi Sweep, thanks for the response. I tried yours as it looks the simplest (for me to understand anyway!)
It works fine with all variants except the old London ones (i.e. AN, ANN, AAN, AANN) for these, I get the following results; the 2 character one gives #Value! error, the 3 character ones have a space before the 1st character and the 4 character ones have a space between the 1st and 2nd characters.

This is probably my fault as I didn't refer to these short postcodes in my initial post - I was still chugging through test data - so sorry if I led you down the wrong road..

I had these variations in mind when I did my clunky version, but I also get a similar result except the 2 character one has a space before the 1st character.
Any mods to your code would be gratefully received as its easy for me to follow! Once again apologies for the lack of info.
Kind regds
Mike

[Marcol]

Where did all the replies come from!!

Try this in a standard module

Option Explicit

Function FormatPostCode(Target)
    Target = WorksheetFunction.Substitute(Target, " ", "")
    Target = Left(Target, Len(Target) - 3) & " " & Right(Target, 3)
    FormatPostCode = Target
End Function

Sub CheckPostCodes()
    Dim LastRow As Long, RowNo As Long
    
    LastRow = Range("J" & Rows.Count).End(xlUp).Row
    For RowNo = 2 To LastRow
        If Range("K" & RowNo) = "United Kingdom" Then
            Range("J" & RowNo) = FormatPostCode(Range("J" & RowNo))
        End If
    Next
End Sub

This will "clean" any existing codes

If you then add this to the worksheet module

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim isect As Range

    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error GoTo ResetApplication
    Application.EnableEvents = False

    Set isect = Intersect(Target, Range("J:J"))
    If Not isect Is Nothing Then
        If Target.Offset(0, 1) = "United Kingdom" Then FormatPostCode Target
    End If

    Set isect = Intersect(Target, Range("K:K"))
    If Not isect Is Nothing Then
        If Target = "United Kingdom" Then
            Target.Offset(0, -1) = FormatPostCode(Target.Offset(0, -1))
        End If
    End If

ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.EnableEvents = True
    Set isect = Nothing
End Sub

This will check codes as you go.

Basically this is a VBa answer to TMShucks' formula

Hope this helps

[MikeWaring]

Hi Marcol, thanks for the info.
Sorry, I'm not very familiar with the steps you recommend - I've inserted a new module for the first part of your reply and pasted that in, but I'm unsure about the next bit; do I insert a second module and paste the second bit into that? and how do I link both these together?
Sorry to sound thick - I'm quite new to VBA so I'm still feeling my way around.

[StephenR]

Mike - refresh your window - you have four solutions now!

[MikeWaring]

Hi Steve, thanks - I've just done that and am trying each of them.

I've just posted a reply to Sweep as I may have bowled everyone a curved ball - I'm still going through my own testing and have found that the old London codes aren't working with mine or Sweep's versions - see my post @ 2:21pm for details.
Oops - Sorry!!

[TMS]

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

[StephenR]

I think mine should cater for all these:

A9 9AA
A99 9AA
AA9 9AA
AA99 9AA
A9A 9AA
AA9A 9AA

[MikeWaring]

Hi Stephen, yes it does all those fine, its just the 2, 3 and 4 character ones that don't work fully. If I could get a solution to cater for these as well I'd be most grateful.
Rgds

[MikeWaring]

Hi Stephen,

I've attached a sample file. All of the examples are actual ones except for the bottom 4 rows - these are the London codes that are complicating matters. Col A shows the raw data as received and Col C is how they should appear (this column is'nt in the live sheet - I've put it in to show the desired results). You can delete the non UK ones if necessary, but I left them in there to show that they need to be ignored when formatting the others.
This sheet is showing the live cols as A and B; normally they are J and K respectively.
Thanks

[StephenR]

Mike - can you provide examples of these?

[TMS]

=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

[Marcol]

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

[MikeWaring]

Hi Marcol, thanks for the info, but I'm still having a "mikewaring" moment.

I write all my modules into the "Personal.xls" worksheet so I can use them with any other worksheet.

This is mainly because my working sheets are replaced every day by that day's new download (using the same filename so we can then export to Access), hence why I can't keep macros in these worksheets.
I copied the 1st module (the function and the sub code below that) into a new module in Personal. Can I now create another module and paste the second part into this? Will this second sub run the first one when I run the second macro?
Thanks

[Marcol]

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?

[MikeWaring]

Hi Marcol, sorry I've just posted you a thread but ddn't see your reply until after I sent it.
The reason I need the postcodes to be exactly correct is that we download files into the Royal Mail and Parcelforce interfaces to create the parcel labels. These 2 systems will only accept the postcodes in the exact format. At the moment we have to manually scrutinise and correct the csv files which is time consuming and inefficient.
Whilst writing this, I've just seen Stephen's post (#23) - all the postcodes are proper - I can't answer for the Post Office as to why they decided not to bring the London ones into line with the usual format; we just have to deal with them!

[TMS]

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


Regards

[MikeWaring]

@TM Shucks:
I tried your formula directly into the spreadsheet, but Excel suggested a slightly different formula:
=UPPER(IF(B2="United Kingdom",IF(LEN(SUBSTITUTE(A2," ",""))>=5,LEFT(SUBSTITUTE(A2," ",""),LEN(SUBSTITUTE(A2," ",""))-3)&" "&RIGHT(SUBSTITUTE(A2," ",""),3),A2),""))

This works fine, but seems to require the formula to be pasted into a separate column; is there any way of converting this to a VBA sub macro so I can run it automatically on Col J without the need for more columns?

@ everyone:
Once again, I thank all who has contributed - it has certainly become clear how little I know about excel formulas / VBA...!

[StephenR]

Well they're not proper postcodes. If you can have all manner of entries you need some sort of logical rule on how to handle them.

EDIT: TMShucks - neat, that seems to do it.

[TMS]

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

[StephenR]

Mike - W1 is only the first part of the postcode, the second bit is missing. I doubt it would help much on an envelope. W1 probably covers thousands of addresses.

See here: http://en.wikipedia.org/wiki/Postcod...United_Kingdom

[Marcol]

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"

Function FormatPostCode(Target)
    If Len(WorksheetFunction.Trim(Target)) > 5 Then
        Target = WorksheetFunction.Substitute(Target, " ", "")
        Target = Left(Target, Len(Target) - 3) & " " & Right(Target, 3)
    End If
    FormatPostCode = UCase(WorksheetFunction.Trim(Target))
End Function

Sub CheckPostCodes()
    Dim LastRow As Long, RowNo As Long

    LastRow = Range("J" & Rows.Count).End(xlUp).Row
    For RowNo = 2 To LastRow
        If Range("K" & RowNo) = "United Kingdom" Then
            Range("J" & RowNo) = FormatPostCode(Range("J" & RowNo))
        End If
    Next
End Sub

[TMS]

@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

[TMS]

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

[MikeWaring]

Hi TM Shucks, I've tried your VBA - it works perfectly on my test data which was a previous day's live data so it seems to be OK. I'll try it on some (copied!) live data next week and let you know if I have any issues.
I'll mark this now as solved; if I need further assistance on this topic I'll open another.

To everyone that has contributed, thank you all so much; everyone will get an "add" to their reputation...
Kindest regards
Mike

[TMS]

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

Sub sCheckPostCodes()

Dim lFR As Long     ' First Row
Dim lLR As Long     ' Last Row
Dim lLC As Long     ' Loop Counter
Dim pcCell As Range
Dim AWF As WorksheetFunction

Set AWF = Application.WorksheetFunction

lFR = 2
lLR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
' For each cell in the range ...
For lLC = lFR To lLR
    ' For convenience, set a range variable to the cell being checked
    Set pcCell = Range("A" & lLC)
    With pcCell
        ' Check if UK
        If .Offset(0, 1) = "United Kingdom" Then
            ' get rid of all spaces
            .Value = Trim(.Value)
            .Value = AWF.Substitute(.Value, " ", "")
            ' Check length of field; London codes will be short
            If Len(.Value) >= 5 Then
                ' Build the reformatted Post Code
                .Value = Left(.Value, Len(.Value) - 3) & _
                         " " & _
                         Right(.Value, 3)
            End If
        End If
        ' Whatever is left, convert to Upper Case
        .Value = UCase(.Value)
    End With
Next 'lLC
Application.ScreenUpdating = True
    
End Sub

Regards

[Marcol]

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

Option Explicit

Function FormatPostCode(Target)
    Dim strChk As String

    With WorksheetFunction
        strChk = .Trim(Target)
        strChk = .Substitute(strChk, " ", "")
    End With

    Target.Interior.ColorIndex = xlNone
    If IsNumeric(Left(strChk, 1)) Then
        FormatPostCode = Target
        Target.Interior.ColorIndex = 3
    Else
        Select Case Len(strChk)
            Case Is < 2
                FormatPostCode = Target
                Target.Interior.ColorIndex = 3
            Case 2, 3
                If IsNumeric(Right(strChk, 1)) Then
                    FormatPostCode = UCase(WorksheetFunction.Trim(strChk))
                Else
                    FormatPostCode = Target
                    Target.Interior.ColorIndex = 3
                End If
            Case 4
                If IsNumeric(Right(strChk, 2)) Then
                    If Not IsNumeric(Right(strChk, 3)) Then
                        FormatPostCode = UCase(WorksheetFunction.Trim(strChk))
                    Else
                        FormatPostCode = Target
                        Target.Interior.ColorIndex = 3
                    End If
                End If
            Case Else
                strChk = Left(strChk, Len(strChk) - 3) & " " & Right(strChk, 3)
                FormatPostCode = UCase(WorksheetFunction.Trim(strChk))
        End Select
    End If
End Function

Sub CheckPostCodes()
    Dim LastRow As Long, RowNo As Long

    LastRow = Range("J" & Rows.Count).End(xlUp).Row
    For RowNo = 2 To LastRow
        If Range("K" & RowNo) = "United Kingdom" Then
            Range("J" & RowNo) = FormatPostCode(Range("J" & RowNo))
        End If
    Next
End Sub

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.

[TMS]

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

[Michael Spedding]

Hi,

I know I have found this late but, is there a way that I could manipulate this to give a true or a false if the postcode is in the correct format? (plus, I don't have a country name and my post codes are in Col F)

I hope someone can help

Thanks,

Michael

[TMS]

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