I thought it could have been something to do with using visual basic but
wasnt sure, how, would I then apply this code to a certain cell?

"Mike H" wrote:

Hi,

try this. Right click the cheet tab, view code and paste this in.


Option Explicit
Sub UK_Postcodes()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = False
.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})"
End With
Set Myrange = ActiveCell
Outstring = ""
Set Collection = RegExp.Execute(ActiveCell.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next

If ActiveCell.Value < "" And ActiveCell.Value = Outstring Then
MsgBox "Valid UK Postcode"
Else
MsgBox "Invalid UK Postcode"
End If
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
End Sub

Works on active cell

Mike
"holmedwa04" wrote:

Hi,

I am trying to make a spreadsheet for a piece of school work, however I am
having trouble trying to make some validation that will allow me to check
that any postcode entered is in the correct format:

Letter, Letter, Number and posibly one more nuimber, space, Number, Letter,
Letter.

Does anyone know how I can validate a cell to do this?

I am aware that this can be easily done is Access however I am not allowed
to use this for this assignment.

Also does anyone know how I could do the same for a phone number? Ensuring
that it is 11 digits and starts with an 0?