Standardizing Postcodes

**mikeyfear** · 05-14-2008, 10:48 AM

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** · 05-15-2008, 03:10 AM

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** · 05-15-2008, 07:51 AM

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.

**royUK** · 05-15-2008, 08:05 AM

It's good to see someone trying to adapt the code. Yoiu can shorten yours by moving the check

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
            If .Characters(4, 1).Text = " " Then GoTo Skip
            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
                    GoTo Skip
            End Select
            .Value = sPostcode
Skip:
        End With
    Next cl
End Sub

**mikeyfear** · 05-15-2008, 08:56 AM

I tried it there but it would need to look for the 4th char from the right, not the left.

I couldn't find what to use to give me that , so if I put it in each specific case since in a 7 char string the 4th from the left = the 4th from the right, and in a 6 char string the 3nd from the left will always be the 4th from the right.

Otherwise I don't think your version would skip the likes of B1 1BB, i.e. 6 chars and the space ought to live in the 3rd char.

Standardizing Postcodes

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thanks!

Nearly there!

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions