British Postal Code

[Mannie]

hi guys

mr. ignorant is back... how do i format cells so they
display the entry in british postal code format. for
example: AB1 2CD. the first number can also be be a 2digit
number!

using excel 97 and i can't for the life of me not figure
it out...

mannie

[David McRitchie]

Hi Manny,
Don't understand the problem. You would have a text
entry for your British postal codes. When you type in
"AB1 2CD" into a cell you would get text even overriding
a number format. What do you see happening that
causes a problem. Zip codes including those for the
US and any identity "number" should be entered and
formatted as text.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Mannie" <mosi...@orange.net> wrote in message news:967c01c19d05$1a3542e0$35ef2ecf@TKMSFTNGXA11...

[mannie]

hi david

ok... this is what i wanna see happen to my cell: i enter
a code like ab12cd and i want it displayed in the cell as
AB1 2CD.

mannie

>.
>

[David McRitchie]

Hi Mannie,
One way would be an Event Macro more information in
http://www.mvps.org/dmcritchie/excel/event.htm

Unlike regular macros which are installed in regular modules,
Worksheet Events are installed with the worksheet by
rightclicking on the sheettab, choose 'view code', and
then paste in your macro.

The following assumes your British postal code is in column 8.

Private Sub Worksheet_Change(ByVal Target As Range)
'British zip code entry: "ab12cd" converts to "AB1 2CD"
If Target.Column <> 8 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If UCase(Left(Target.Value, 1)) < "A" Then Exit Sub
If Len(Target.Value) <> 6 Then Exit Sub
If Mid(Target.Value, 4, 1) = " " Then Exit Sub
Target.Value = UCase(Left(Target.Value, 3) & _
" " & Right(Target.Value, 3))
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"mannie" <mosi...@orange.net> wrote ...

[Roger Govier]

Hi David

The British Postal Code system can be 7 characters long, with up to 2 digits
following the first 2 letters.
e.g. NP7 9YP
NP15 3EY

I think the following minor amendment of the code you provided will deal
with both cases.

Private Sub Worksheet_Change(ByVal Target As Range)
'British zip code entry: "ab12cd" converts to "AB1 2CD"

Dim dlen As Long, tlen As Long
dlen = Len(Target.Value)
If dlen = 6 Then tlen = 3 Else tlen = 4

If Target.Column <> 8 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If UCase(Left(Target.Value, 1)) < "A" Then Exit Sub

If Len(Target.Value) > 7 Then Exit Sub

If Mid(Target.Value, 4, 1) = " " Then Exit Sub

Target.Value = UCase(Left(Target.Value, tlen) & _

" " & Right(Target.Value, 3))
End Sub

An alternative formula solution, using an additional column, would be to
place in column B
=IF(LEN(A2)=6,UPPER(LEFT(A2,3)&" "&RIGHT(A2,3)),UPPER(LEFT(A2,4)&"
"&RIGHT(A2,3)))

--
Regards

Roger Govier
Technology 4 U W98 XL2K

"David McRitchie" <dmcri...@msn.com> wrote in message
news:uLuiK7QnBHA.2168@tkmsftngp05...

[Alan Perkins]

Hi All,

I think you'll find that the post codes are a bit more complex than that.
For instance, W1A 1AA is the one of the London
post codes (Radio 1 in my youth). Things can get quite complicated whne you
apply all possible combinations!

Alan P.

"Roger Govier" <roger....@btinternet.com> wrote in message
news:OdMw#YRnBHA.2460@tkmsftngp04...

[David McRitchie]

Hi Alan,
I see what you mean, such as Birmingham has "B" followed by
1 or 2 digits in first part -- from what I see
http://www.afd.co.uk/pcsearch.htm
Is the second set always 3 characters?

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Alan Perkins" <al...@perkins55.freeserve.co.uk> wrote in message news:usnwHZTnBHA.1600@tkmsftngp07...

[Debra Dalgleish]

BAsed on info from the Royal Mail site, the Post Code is 1 or 2 letters,
followed by 1 or 2 characters, then 1 number and 2 letters. So you could
use the right 3 as an anchor for the change:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
Target.Value = UCase(Left(Target.Value, Len(Target.Value) - 3) _
& " " & Right(Target.Value, 3))
Application.EnableEvents = True
End If
End Sub

[David McRitchie]

Thanks Debra, I couldn't find that information, but I don't
think Manie wants it limited to working in a single cell.
Here is what I came up with I made it longer hoping that
the code would run faster. This is running very slow for
me, but my system is somewhat short
on resources so I don't know if it is just me.

The following assumes your British postal code is in column 8.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 8 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Dim code As String, L As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
code = UCase(Trim(Target.Value))
L = Len(code)
If Left(code, 1) < "A" Then GoTo done
If L > 6 Then GoTo done
If L < 5 Then GoTo done
If InStr(1, code, " ", vbTextCompare) > 0 Then GoTo done
code = Left(code, L - 3) & " " _
& Right(code, 3)
done:
Target.Value = code
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Debra Dalgleish" <d...@contextures.com> wrote ...
> Based on info from the Royal Mail site, the Post Code is 1 or 2 letters,

> followed by 1 or 2 characters, then 1 number and 2 letters. So you could
> use the right 3 as an anchor for the change:
> >>>

[Andy Brown]

Hi David,

>Is the second set always 3 characters?

Yes. The format is always (i) 2, 3 or 4 characters (ii) a space (iii) 3
characters. AFAIK, both sets of characters are a mix of numbers & letters
with 1 exception - National Girobank in Bootle =
GIR 0AA.

I expect your code & Debra's work fine, but OP asked about formatting. I
tried a custom format of
0000 000, which worked fine for numbers only, but fell over when I threw
text in. So, out of curiosity, is there any way this can be done with
formatting? Failing that, with mirrors? <g>

Cheers,
Andy