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:

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!!!