Standardizing Postcodes

[mikeyfear]

I have a file of 60000 postcodes, which I've created a page to sum up certain ranges that we're interested in.

The postcodes should all be in the format AA11 1AA, but the people entering in the info are not very conistant. Is there anyway I can have a macro check column B for each cell to ensure that it's in the format AA11 1AA, and where its been entered as AA111AA, add the missing space?

*edit*
I forgot about postcodes like A11 instead of AA11, and AA1 instead of AA11. The space therefore needs to be inserted 3 from the right, if their isnt one already. Any other spaces in the cell might as well be deleted while we're at it!

[royUK]

Try this

Option Explicit
Sub amendPC()
    Dim cl     As Range
    Dim rng    As Range
    Dim sPostcode As String

    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    For Each cl In rng
        With cl
            Select Case Len(.Value)
                Case 7
                    sPostcode = Left(.Value, 4) & " " & Right(.Value, 3)

            End Select
            .Value = sPostcode
        End With
    Next cl
End Sub

[royUK]

You can prevent input errors by using the worksheet_change event

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sPostcode As String

    If Application.Intersect(Target, Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    '
   Application.EnableEvents = False
    With Target
        Select Case Len(.Value)
            Case 7
                sPostcode = Left(.Value, 4) & " " & Right(.Value, 3)
'            Case Else
        End Select
        .Value = sPostcode
    End With
    Application.EnableEvents = True

End Sub

[mikeyfear]

Thanks again Roy.

They're not input here, but spat out of our customers delivery system in 64000 row chunks. Really not friendly to work with it at all, I will need some help again shortly I'm sure, because my unefficient methods of working dont agree with so much data, I'm crashing the PC every 5 mins at the moment... but I digress. Thanks!

[mikeyfear]

I have update your code Roy to include the 5 and 6 digit postcodes that could be missing spaces too, see below:

Option Explicit
Sub amendPC()
    Dim cl     As Range
    Dim rng    As Range
    Dim sPostcode As String

    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    For Each cl In rng
        With cl
                        
                        Select Case Len(.Value)
                Case 7
                    sPostcode = Left(.Value, 4) & " " & Right(.Value, 3)
                Case 6
                    sPostcode = Left(.Value, 3) & " " & Right(.Value, 3)
                Case 5
                    sPostcode = Left(.Value, 2) & " " & Right(.Value, 3)
                Case Else
                    cl.Value = sPostcode
                    
            End Select
            .Value = sPostcode
        End With
    Next cl
End Sub

This almost works but if the postcode is a correctly formatted char one like:
B6 2JB instead of a B662JB, it puts an uneeded extra space in the post code. Is it possible to have the macro ignore any cell entry where the forth char from the right is already a " ". I tried and failed to acheive this.

[mikeyfear]

Smug face, I may have managed to help myself for a change. This appears to do what I want to. I shall get the hang of this vba malarky eventually. Code for reference:

Option Explicit
Sub amendPC()
    Dim cl     As Range
    Dim rng    As Range
    Dim sPostcode As String

    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    For Each cl In rng
        With cl
                          
                        Select Case Len(.Value)
                Case 7
                    If cl.Characters(4, 1).Text = " " Then GoTo Skip
                    sPostcode = Left(.Value, 4) & " " & Right(.Value, 3)
                Case 6
                    If cl.Characters(3, 1).Text = " " Then GoTo Skip
                    sPostcode = Left(.Value, 3) & " " & Right(.Value, 3)
                Case 5
                    sPostcode = Left(.Value, 2) & " " & Right(.Value, 3)
                Case Else
                    GoTo Skip

            End Select
            .Value = sPostcode
Skip:
        End With
    Next cl
End Sub

Seems to work, so I'll leave that up as an example should anyone want to do the same. Thanks for the inital help.