i have read various examples of validating postal codes, but none seem to
work for me.

what i am trying to do is in one cell (A1), i enter a canadian postal code
in A1 and it first checks to make sure that the format is correct (letter,
number, letter, number, letter, number) z9z9z9.

if it passes that check, then the format changes to CAPS and a space between
the third and fourth characters. so z9z9z entered in A1 would be first
validated then would display Z9Z 9Z9 in the same cell A1.

i have tried some examples from the forum, but nothing is working for me.

any help would be appreciated.

This is really ugly, but it will at least give you an idea of how to do what
you want. It seems to work as you described. Right-click on the worksheet
you want to put this code in, and select "View Code". Then paste this code
in. You'll have to modify it to look at whatever cell or cells your postal
code is in.

HTH,

Eric

Private Sub Worksheet_Change(ByVal Target As Range)
Dim chk1 As Boolean, chk2 As Boolean, chk3 As Boolean
Dim chk4 As Boolean, chk5 As Boolean, chk6 As Boolean
'
' Wherever you have the postal code...
If (Not Application.Intersect(Target, Range("A1")) Is Nothing) Then
If (Target.Text = "") Then Exit Sub ' Probably want some quality
checks...
If (Len(Target.Text) < 6) Then Exit Sub
' First part a digit?
chk1 = IsNumeric(Left(Target.Text, 1))
' Second part a letter (upper or lower case)
chk2 = (Asc(UCase(Mid(Target.Text, 2, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 2, 1) <= 90)))
' Third part a digit?
chk3 = IsNumeric(Mid(Target.Text, 3, 1))
' Fourth part a letter (upper or lower case)
chk4 = (Asc(UCase(Mid(Target.Text, 4, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 4, 1) <= 90)))
' Fifth part a digit?
chk5 = IsNumeric(Mid(Target.Text, 5, 1))
' Sixth part a letter (upper or lower case)
chk6 = (Asc(UCase(Mid(Target.Text, 6, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 6, 1) <= 90)))
If (chk1 And chk2 And chk3 And chk4 And chk5 And chk6) Then
Target = UCase(Left(Target.Text, 3)) & " " &
UCase(Right(Target.Text, 3))
End If
End If
End Sub

If your test code is in A1, use the following formula in B1:


=AND(LEN(A1)=6,NOT(ISNUMBER(1*MID(A1,1,1))),ISNUMB ER(1*MID(A1,2,1)),
NOT(ISNUMBER(1*MID(A1,3,1))),ISNUMBER(1*MID(A1,4,1 )),
NOT(ISNUMBER(1*MID(A1,5,1))),ISNUMBER(1*MID(A1,6,1 )))

For readability, the formula is split into several lines. In Excel, it
should be on a single line.

The formula will return TRUE or FALSE indicating whether the value in
A1 is a valid postal code.

Then, you can use the following formula in C1 to properly format the
valid postal code:

=IF(B1,UPPER(LEFT(A1,3)&" "&RIGHT(A1,3)),"invalid code")

You can combine these into one formula.

=IF(AND(LEN(A1)=6,NOT(ISNUMBER(1*MID(A1,1,1))),ISN UMBER(1*MID(A1,2,1)),
NOT(ISNUMBER(1*MID(A1,3,1))),ISNUMBER(1*MID(A1,4,1 )),
NOT(ISNUMBER(1*MID(A1,5,1))),ISNUMBER(1*MID(A1,6,1 )))
,UPPER(LEFT(A1,3)&" "&RIGHT(A1,3)),"invalid code")

It will return the properly formatted postal code from A1 if it is
valid or "invalid code" if A1 is not valid.

If you're looking for a code solution, use the following function:

Function PostalCode(S As String) As Variant
If (Len(S) = 6) And _
(Not (IsNumeric(Mid(S, 1, 1)))) And _
(IsNumeric(Mid(S, 2, 1))) And _
(Not (IsNumeric(Mid(S, 3, 1)))) And _
(IsNumeric(Mid(S, 4, 1))) And _
(Not (IsNumeric(Mid(S, 5, 1)))) And _
(IsNumeric(Mid(S, 6, 1))) Then

PostalCode = UCase(Left(S, 3) & " " & Right(S, 3))
Else
PostalCode = CVErr(xlErrValue)
End If
End Function


If S is a valid code, the formatted result is returned. Otherwise, a
#VALUE error is returned. You can call this from a worksheet cell with
=PostalCode(A1)


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 24 Feb 2009 13:11:04 -0800, jat
wrote:

i have read various examples of validating postal codes, but none seem to
work for me.

what i am trying to do is in one cell (A1), i enter a canadian postal code
in A1 and it first checks to make sure that the format is correct (letter,
number, letter, number, letter, number) z9z9z9.

if it passes that check, then the format changes to CAPS and a space between
the third and fourth characters. so z9z9z entered in A1 would be first
validated then would display Z9Z 9Z9 in the same cell A1.

i have tried some examples from the forum, but nothing is working for me.

any help would be appreciated.

The VBA code in my previous post can be shortened to

Function PostalCode(S As String) As Variant
If S Like "[A-Za-z]#[A-Za-z]#[A-Za-z]#" Then
PostalCode = UCase(Left(S, 3) & " " & Right(S, 3))
Else
PostalCode = CVErr(xlErrValue)
End If
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 24 Feb 2009 13:11:04 -0800, jat
wrote:

i have read various examples of validating postal codes, but none seem to
work for me.

what i am trying to do is in one cell (A1), i enter a canadian postal code
in A1 and it first checks to make sure that the format is correct (letter,
number, letter, number, letter, number) z9z9z9.

if it passes that check, then the format changes to CAPS and a space between
the third and fourth characters. so z9z9z entered in A1 would be first
validated then would display Z9Z 9Z9 in the same cell A1.

i have tried some examples from the forum, but nothing is working for me.

any help would be appreciated.

Whenever you use the _Change event to modify the Target cell, you
always want to turn off events. Otherwise, the Change code change
Target, which triggers Change, which changes Target, which triggers
Change, and on and on until VBA gives up when it runs out of stack
space.

Application.EnableEvents = False
Target.Value = whatever
Application.EnableEvents = True

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 24 Feb 2009 14:09:01 -0800, egun
wrote:

This is really ugly, but it will at least give you an idea of how to do what
you want. It seems to work as you described. Right-click on the worksheet
you want to put this code in, and select "View Code". Then paste this code
in. You'll have to modify it to look at whatever cell or cells your postal
code is in.

HTH,

Eric

Private Sub Worksheet_Change(ByVal Target As Range)
Dim chk1 As Boolean, chk2 As Boolean, chk3 As Boolean
Dim chk4 As Boolean, chk5 As Boolean, chk6 As Boolean
'
' Wherever you have the postal code...
If (Not Application.Intersect(Target, Range("A1")) Is Nothing) Then
If (Target.Text = "") Then Exit Sub ' Probably want some quality
checks...
If (Len(Target.Text) < 6) Then Exit Sub
' First part a digit?
chk1 = IsNumeric(Left(Target.Text, 1))
' Second part a letter (upper or lower case)
chk2 = (Asc(UCase(Mid(Target.Text, 2, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 2, 1) <= 90)))
' Third part a digit?
chk3 = IsNumeric(Mid(Target.Text, 3, 1))
' Fourth part a letter (upper or lower case)
chk4 = (Asc(UCase(Mid(Target.Text, 4, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 4, 1) <= 90)))
' Fifth part a digit?
chk5 = IsNumeric(Mid(Target.Text, 5, 1))
' Sixth part a letter (upper or lower case)
chk6 = (Asc(UCase(Mid(Target.Text, 6, 1))) = 65 And _
Asc(UCase(Mid(Target.Text, 6, 1) <= 90)))
If (chk1 And chk2 And chk3 And chk4 And chk5 And chk6) Then
Target = UCase(Left(Target.Text, 3)) & " " &
UCase(Right(Target.Text, 3))
End If
End If
End Sub

this code works, but it works when the target cell is different then the
source cell ex. the source cell is a1 (where the user enters the z9z9z9) and
=postalcode(A1) is in A2, it does return Z9Z 9Z9. because this code is
smaller and easier to understand, i'll work with this one and add a
cut/copy/paste back to a1...

i make it sound so easy..

jat


"Chip Pearson" wrote:


The VBA code in my previous post can be shortened to

Function PostalCode(S As String) As Variant
If S Like "[A-Za-z]#[A-Za-z]#[A-Za-z]#" Then
PostalCode = UCase(Left(S, 3) & " " & Right(S, 3))
Else
PostalCode = CVErr(xlErrValue)
End If
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 24 Feb 2009 13:11:04 -0800, jat
wrote:

i have read various examples of validating postal codes, but none seem to
work for me.

what i am trying to do is in one cell (A1), i enter a canadian postal code
in A1 and it first checks to make sure that the format is correct (letter,
number, letter, number, letter, number) z9z9z9.

if it passes that check, then the format changes to CAPS and a space between
the third and fourth characters. so z9z9z entered in A1 would be first
validated then would display Z9Z 9Z9 in the same cell A1.

i have tried some examples from the forum, but nothing is working for me.

any help would be appreciated.