Excel Custom Formats

[Guest]

I am trying to create a custom format for a postal code. I have a postal code entered in the format of: A1A1A1 and I would like to create the custom format so it looks like this: A1A-1A1. I don't know how to do this with letters. I can do this with all numbers. The letters are all different. Any help I find is to enter text within the cell. I want don't want to entere text. I do not know how to enter individual various letters into the format. Can someone help me solve this?

 

[Chip Pearson]

As far as I know, you can't do this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

I am trying to create a custom format for a postal code. I

have a postal code entered in the format of: A1A1A1 and I would
like to create the custom format so it looks like this: A1A-1A1.
I don't know how to do this with letters. I can do this with all
numbers. The letters are all different. Any help I find is to
enter text within the cell. I want don't want to entere text. I
do not know how to enter individual various letters into the
format. Can someone help me solve this?

 

[Frank Kabel]

Hi
if you have characters in your cell: no chance to do this with a custom
format (would work only for numbers). The only thing which could work
would be to use an event procedure (the worksheet_change event) and
change the cell value after your data entry. e.g. put the following
code in your worksheet module (not in a standard module):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
on error goto errhandler
With Target
If len(.Value)=6 Then
Application.EnableEvents = False
.value = left(.value,3) & "-" & right(.value,3)
End If
End With

errhandler:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

I am trying to create a custom format for a postal code. I have a

postal code entered in the format of: A1A1A1 and I would like to create
the custom format so it looks like this: A1A-1A1. I don't know how to
do this with letters. I can do this with all numbers. The letters are
all different. Any help I find is to enter text within the cell. I
want don't want to entere text. I do not know how to enter individual
various letters into the format. Can someone help me solve this?