Oops, I didn't absorb the described objective. Hopefully this works will
convert:

12123456789a
to
12-123456789-A

and
12123456
to
12-123456###-?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sOrig As String, sNum As String, sNew As String
Dim rng As Range, cell As Range

On Error GoTo errExit
Set rng = Intersect(Range("A1:A10"), Target)
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
sOrig = cell.Value
sNew = Replace(sOrig, "-", "")
sNew = Replace(sNew, "#", "")
sNum = Int(Val(sNew))
sNew = UCase(Mid(sNew, Len(sNum) + 1, 1))
If UCase$(sNew) = LCase(sNew) Then sNew = ""
sNew = "-" & sNew & "?"
sNum = Left$(sNum & "###########", 11)
sNum = Left$(sNum, 2) & "-" & Right$(sNum, 9)
sNew = sNum & Left$(sNew, 2)
If sNew < sOrig Then cell = sNew ' only if necessary
Next
End If

errExit:
Application.EnableEvents = True

End Sub

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Although a cell numberformat can display alpha's within the format, the
cell
contents must be a real number without any alpha's, so your last variable
alpha precludes Numberformat being of any use

Try the following in the sheet module. Rt-click the sheet tab, View code,
and paste into the sheet module.

Change "A1:A10" to the cell or block of cells you want to process.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sOrig As String, sNum As String, sNew As String
Dim rng As Range, cell As Range

On Error GoTo errExit
Set rng = Intersect(Range("A1:A10"), Target)
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
sOrig = cell.Value
sNew = Replace(sOrig, "-", "")
sNew = Replace(sNew, "#", "")
sNum = Int(Val(sNew))
sNew = UCase(Mid(sNew, Len(sNum) + 1, 1))
If UCase$(sNew) = LCase(sNew) Then sNew = ""
sNew = sNew & "?"
sNum = Left$(sNum & "#########", 9)
sNum = Left$(sNum, 2) & "-" & Right$(sNum, 7)
sNew = sNum & Left$(sNew, 1)
If sNew < sOrig Then cell = sNew ' only if necessary
resNext:
Next
End If

errExit:
Application.EnableEvents = True

End Sub


As written, #'s are included in case of missing numerals, and a ? for a
missing last alpha.

If(?) it does roughly what you want I have no doubt it could be made more
efficient with fewer lines of code

Regards,
Peter T

"Rick" wrote in message
...
To Rick & Peter:
I have a cell that is for entry by keyboard. It will contain a
billing
reference number, that has a format of 2digits, a dash, 9digits, a dash,
and
a alpha character a thru z. All I want to do is enter that code as
follows:12123456789a, and have it show in the cell as 12-123456789-A, or
12-123456789-B, or 12-123456789-Z

"Peter T" wrote:

Try and describe what you have and what you have want to achieve.

Regards,
Peter T

"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