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.