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?

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?

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?

It sounds like you're trying to validate that postal codes have one of the
following two formats: "AAN NAA" or "AANN NAA" where A is alpha and N is
numeric.

How about this formula solution. Assuming your postal codes are in column A
starting in A1, write the following formula in B1 and copy down.

=AND(ISERROR(VALUE(LEFT(B1,1))),ISERROR(VALUE(MID( B1,2,1))),ISNUMBER(VALUE(MID(B1,3,1))),IF(LEN(B1)= 7,MID(B1,4,1)="
",ISNUMBER(VALUE(MID(B1,4,1)))),IF(LEN(B1)=7,ISNUM BER(VALUE(MID(B1,5,1))),MID(B1,5,1)="
"),IF(LEN(B1)=7,ISERROR(VALUE(MID(B1,6,1))),ISNUMB ER(VALUE(MID(B1,6,1)))),ISERROR(VALUE(MID(B1,7,1)) ),ISERROR(VALUE(MID(B1,8,1))))

If there are other possible formats, you'll have to modify the formula to
accommodate.

"holmedwa04" wrote:

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?

Every month I need to validate international postal codes on an Excel 2003
spreadsheet for about 25 countries. Some of these countries have numerous
acceptable formats (up to about 20 or so) using letters and numbers and
sometimes the initials of the country. For example:

Finland
nnnnn
FI nnnnn
FI-nnnnn
FInnnnn
FIN nnnnn
FIN-nnnnn
FINnnnnn

Canada
lnl nln
lnl-nln
lnlnln

l = letter
n = number

I have looked at your formula below and I'm not sure how to modify it to my
needs. Can you help me?
--
JJ Johnson


"MrAcquire" wrote:

It sounds like you're trying to validate that postal codes have one of the
following two formats: "AAN NAA" or "AANN NAA" where A is alpha and N is
numeric.

How about this formula solution. Assuming your postal codes are in column A
starting in A1, write the following formula in B1 and copy down.

=AND(ISERROR(VALUE(LEFT(B1,1))),ISERROR(VALUE(MID( B1,2,1))),ISNUMBER(VALUE(MID(B1,3,1))),IF(LEN(B1)= 7,MID(B1,4,1)="
",ISNUMBER(VALUE(MID(B1,4,1)))),IF(LEN(B1)=7,ISNUM BER(VALUE(MID(B1,5,1))),MID(B1,5,1)="
"),IF(LEN(B1)=7,ISERROR(VALUE(MID(B1,6,1))),ISNUMB ER(VALUE(MID(B1,6,1)))),ISERROR(VALUE(MID(B1,7,1)) ),ISERROR(VALUE(MID(B1,8,1))))

If there are other possible formats, you'll have to modify the formula to
accommodate.

"holmedwa04" wrote:

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?