UK post code re-format

[tmac999]

I need a macro to re-format UK postcode data to the form:
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

The issues with the data are double spaces, triple or no spaces between the outward code and inward code and capitalisation. Also some entries have spaces where there should be no spaces e.g M 1 1AA from the first example above.

Any help greatly received.

 

[Andrew Poulsom]

Welcome to the Board.

Maybe try:

=UPPER(REPLACE(SUBSTITUTE(A1," ",""),LEN(SUBSTITUTE(A1," ",""))-2,0," "))

 

[tmac999]

Brilliant works a treat!!

 

[al_b_cnu]

Hi,

If you want it to correctly space the postcode, try a UDF:

Option Explicit

Function FormatPostcode(ByVal PostCode As String) As Variant
'AN NAA M1 1AA
'ANN NAA M60 1NW
'AAN NAA CR2 6XH
'AANN NAA DN55 1PT
'ANA NAA W1A 1HQ
'AANA NAA EC1A 1BB
Dim iPtr As Integer
Dim sInput As String, sChar As String, sMask As String

For iPtr = 1 To Len(PostCode)
    sChar = UCase$(Mid$(PostCode, iPtr, 1))
    If IsNumeric(sChar) Then
        sMask = sMask & "9"
        sInput = sInput & sChar
    ElseIf sChar<> LCase$(sChar) Then
        sMask = sMask & "A"
        sInput = sInput & sChar
    End If
Next iPtr

Select Case sMask
Case "A99AA"            'AN NAA M1 1AA
    FormatPostcode = Left$(sInput, 2) & " " & Right$(sInput, 3)
    
Case "A999AA", "A9A9AA"            'ANN NAA M60 1NW or ANA NAA W1A 1HQ
    FormatPostcode = Left$(sInput, 3) & " " & Right$(sInput, 3)
    
Case "AA99AA"           'AAN NAA CR2 6XH
    FormatPostcode = Left$(sInput, 3) & " " & Right$(sInput, 3)
    
Case "AA999AA", "AA9A9AA"          'AANN NAA DN55 1PT or AANA NAA EC1A 1BB
    FormatPostcode = Left$(sInput, 4) & " " & Right$(sInput, 3)
    
Case Else
    FormatPostcode = CVErr(xlErrNA)
End Select
End Function

For example:

Excel Workbook
	A	B
1	M11aa	M1 1AA
2	m691nw	M69 1NW
3	cr26xh	CR2 6XH
4	dn551pt	DN55 1PT
5	w1a1hq	W1A 1HQ
6	ec1a1bb	EC1A 1BB
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=FormatPostcode(A1)
B2	=FormatPostcode(A2)
B3	=FormatPostcode(A3)
B4	=FormatPostcode(A4)
B5	=FormatPostcode(A5)
B6	=FormatPostcode(A6)