Ah, now I see what you want. Okay, I don't think you can do this with a cell
format, but you can do it with a worksheet event procedure. Right-click the
tab for the sheet where you want this functionality to be on and copy/paste
this code into the code window that appears....
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then Exit Sub
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = Format(UCase(Target.Value), "&&-&&&&&&&&&-&")
Whoops:
Application.EnableEvents = True
End Sub
Note that I set this up for Column D (that is what the '< 4' test is doing)
but you can change this as needed. Now, go back to your sheet and type in
one of your numbers into a cell in Column D (don't type in any dashes, the
code will insert them for your)... hit Enter, Tab or click into a different
cell and watch what happens to your entry.
Rick
"Rick" wrote in message
...
Rick:
I will be typing the entire number and letter in the cell, I have the
first
part of the format which is 00-000000000-? the question mark is what is
missing. what character do I use to have a single alpha appear in that
position.
"Rick Rothstein (MVP - VB)" wrote:
Then I guess I am not following what you want or need. What are you
physically going to type into the cell you asked to be able to format?
Where
does the A, B, C, D you asked about come from? You are going to have to
give
us a little more detail about your layout, what is typed where, what you
want to look like what where, etc.
Rick
"Rick" wrote in message
...
The number part as well as the alpha part can be anynumber and any
alpha.
It
is a trace code for medical billing, but the format will always be the
same.
"Rick Rothstein (MVP - VB)" wrote:
Is the "number" part always going to be 07-108029827? In other words,
what
do you want to TYPE into the cell and what do you want appear? If the
number
part is not the same all the time, where is it coming from?
Rick
"Rick" wrote in message
...
Is there anyone out there that can tell me how to create a format
that
does
this:
07-108029827-A it's the A that needs to be a substitue alpa
character,
because the A may have to be a B, or C, or D.
Thanks