Format Cell for UK Postcode

[andbee]

Hello Folks,

I have been creating an enquiry form for use by our Sales Teams but I have problem in that I can not format a cell in the UK Postcode format.

The format would be 2 capital letters/1 or 2 numbers/space 1 or 2 numbers/2 capital letters.

Does anyone know if this can be done?

I have seen several VB Codes which will change the format after it has been entered, but I want to force the users to enter the Text in the correct format to begin with.

Thanks

 

[Michael M]

Have a look at this previous thread


http://www.mrexcel.com/forum/excel-questions/475161-uk-post-code-re-format.html

 

[andbee]

Have a look at this previous thread


http://www.mrexcel.com/forum/excel-questions/475161-uk-post-code-re-format.html

Hi

Thanks for this.

This was one of the things I looked at before, but I am sure I used to be able to use a 'Mask' over a cell so that the users input always formatted correctly.

Thanks

 

[Michael M]

Can you post a couple of examples of what they should look like

 

[andbee]

Hi

Example 1
AB11 1ZX

Example 2
AB1 1ZX

Example 3
A1 1ZX

Thanks

 

[Michael M]

Assuming the user types the data into column "A"
Try this once the data is been inserted....it will do the whole column

Sub MM1() 'code by StephenR
Dim r As Long
For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        With CreateObject("vbscript.regexp")
            .Global = True
            .Pattern = "[A-Z]{1,2}[0-9]{1,2}\s[0-9][A-Z]{2}"
            If Not .test(Cells(r, "A")) Then
                Cells(r, "A") = UCase(Left(Cells(r, "A"), Len(Cells(r, "A")) - 3) & " " & Right(Cells(r, "A"), 3))
           End If
       End With
Next r
End Sub

 

[andbee]

Hi Michael,

Many thanks for this, but looking at this now I can see where I have possibly phrased my problem badly!

I have a single cell that I want to force users to enter the postcode in the correct format. I don;t have a column of data just a single cell. Is there no way of formatting that one cell without having to resort to coding?

Thanks for your help, it is much appreciated.

Andrew

 

[Michael M]

I don't believe so...I'm no Custom Format expert, but I believe with the chance of users inputting lowercase, propercase, extra spaces, etc....it would be too difficult to control
this will adjust cell A1 only, and automatically, once a user has input the data
Paste into the sheet module

Private Sub worksheet_change(ByVal target As Range)
 If Intersect(target, Range("A1")) Is Nothing Then Exit Sub
 With CreateObject("vbscript.regexp")
     .Global = True
     .Pattern = "[A-Z]{1,2}[0-9]{1,2}\s[0-9][A-Z]{2}"
     If Not .test(Cells(1, 1)) Then
         Cells(1, 1) = UCase(Left(Cells(1, 1), Len(Cells(1, 1)) - 3) & " " & Right(Cells(1, 1), 3))
    End If
End With
End Sub