[mso] Re: Format a cell with characters & numbers

To change the information you enter in a cell you need a format or
style.  Unfortunately these options only apply to numbers - text in
Excel is entered as entered.  You can use a macro tied to the worksheet
change event to get the text based postal code to be the way you want.

The Worksheet_Change event is a procedure of the Worksheet Object and as
such, the Worksheet_Change procedure MUST reside in the private module
of the Worksheet Object.

Accessing the Private Module of the Worksheet Object

There are at least 2 ways to gain access to the private module of the
Worksheet Object.

1) While in Excel proper, right click on the Worksheet name tab and
choose View Code.

2) While in the VBE double click the Module called Sheet* (Sheet*), seen
in the Project Explorer (View>Project Explorer (Ctrl+R)) under Microsoft
Excel Objects.

Once here, you can select "Worksheet" from the Object drop down list,
located in the top left of the module pane. After you have selected
"Worksheet" Excel will default to;

Private Sub Worksheet_Change(ByVal Target As Range)

This code  will format a postal code entered in a cell the way you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LeftPart As String
    Dim RightPart As String
    If Target.Value <> "" Then
        On Error Resume Next
        'Turn off ALL events so the the change does not put the code
into a loop.
        Application.EnableEvents =3D False
        Target.Replace What:=3D" ", Replacement:=3D"", LookAt:=3DxlPart, =
_
                       SearchOrder:=3DxlByRows, MatchCase:=3DFalse,
SearchFormat:=3DFalse, _
                       ReplaceFormat:=3DFalse

        RightPart =3D Right(Target.Value, 3)
        LeftPart =3D Left(Target.Value, Len(Target.Value) - 3)
        Target.Value =3D UCase(LeftPart & " " & RightPart)
        'Turn events back on
        Application.EnableEvents =3D True
        'Allow run time errors again
        On Error GoTo 0
    End If
End Sub

Since this runs on any cell you change, you might want to add some code
to check that you are in the postal code cell (column) so you don't end
up formatting every cell like a postal code.

Rob Tillotson


Robert Tillotson
BAE-IT
Groton Field Office
860-441-2207

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Geoff Culbertson
Sent: Monday, August 13, 2007 5:47 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Format a cell with characters & numbers

Thanks John - I understand how your formula works now, having played
around
with it for a while. However I can't see how it can be applied to a cell
where the figures are actually input.

--=20
Geoff Culbertson
Petersfield, Hampshire, U.K.


-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf
Of joby john
Sent: 13 August 2007 07:57
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Format a cell with characters & numbers

Hello,
  I am not sure if formating is possible, I would have used the formula=20
      =3D(UPPER(LEFT(A5,3))&"  "&UPPER(RIGHT(A5,3)))  Assuming  original
input
is in A5
  =20
  Regards,
  John
 =20
Geoff Culbertson <glio@xxxxxxxxxxxx> wrote:
  I am trying to format a cell so that it will automatically correctly
format
the cell as a UK Post code when it is input e.g. enter PO62SA or po62sa,
would appear as PO6 2SA, i.e. two letters, one or two numbers, a space,
then
one number and two letters. I would ideally like it to convert lower
case
letters to upper case automatically as well. Is any of this possible?

I have seen the Special Number option in Cell/format, but under
English(UK)
there are no options. Can you add your own special formatting to this
option?

TIA for any suggestions.

--=20
Geoff Culbertson
Petersfield, Hampshire, U.K.


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow
you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your
email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


      =20
---------------------------------
Pinpoint customers who are looking for what you sell.=20

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top.  This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group.  This group will not allow for posting of emails, but will allow
you
to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your
email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top.  This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group.  This group will not allow for posting of emails, but will allow
you to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
FILE SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your
email messages, read these instructions that explain why and how to fix
it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: