Format Cells for UK Postcode

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