Excellent. This code worked perfectly! Thank you very much for your help!

"Dave Peterson" wrote:

You could use a worksheet_Change event.

Rightclick on the worksheet tab that should have this behavior. Select view
code. Paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
Dim myIntersect As Range
Dim myCell As Range
Dim myStr As String

Set myRngToCheck = Me.Range("a:a")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

Application.EnableEvents = False
For Each myCell In myIntersect.Cells
myStr = ""
If UCase(myCell.Value) Like "[A-Z]#############" Then
'do the conversion
myStr = UCase(Left(myCell.Value, 1)) _
& Format(Mid(myCell.Value, 2), "000-0000-0000-00")
myCell.Value = myStr
End If
Next myCell

ErrHandler:
Application.EnableEvents = True

End Sub

I checked all of column A. Change this line to match your input range:

Set myRngToCheck = Me.Range("a:a")




excel user wrote:

That's unfortunate. You'd think MS would have the ability to store driver's
license numbers in their correct format. They should have the formats
pre-defined just as they do with "Social Securtiy Number" and "Zip Code".
It's pretty rare I find something that MS Excel cannot do. What about using
an event driven macro - would that work? How would it work? Would you be
able to use the same cell?

"Ron Rosenfeld" wrote:

On Wed, 16 Jul 2008 08:58:05 -0700, excel user <excel
wrote:

I need to create a custom number format for a wisconsin driver's license
number (i.e. "S123-4567-8912-34") How can this be done?

000-0000-000-00 will do for the numeric part of the number,

\S000-0000-000-00 will do for the above where the numeric part is preceded by
the letter "S". (You would just enter the numbers)

but I don't think you can have a variable preceding letter in the format.

If you put your letter in A1, and the number in B1, then perhaps:


C1: =TEXT(B1,"\"&UPPER(A1)&"000-0000-000-00")

--ron


--

Dave Peterson