Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "B1:B10" 'adjust range to suit
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell
.Value = Left(Cell, Len(Cell) - 2) & "-" & Right(Cell, 2)
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub

No error-checking for entries less than or greater than 10 characters.


Gord Dibben MS Excel MVP


On Tue, 25 May 2010 12:24:11 -0700, Brad E.
wrote:

My Understanding: When using VALUES, one can format a cell to display a SSN
or ZIP+4 using Format Cells Custom. Of course, Excel has a couple built
in, including these two, but you could make any format you want as long as
the entry is a value.

My Problem: I have part numbers which start with an Alpha character
followed by 7 digits, then a dash (minus) and two more digits. Is there a
way to format my cell entry to include the dash when the user just enters the
10-character part number?

Specific Example: If my part number if P1234567-89, users sometimes enter
P123456789. I would like to custom format the cell to automatically put a
dash in if it is not included. Not all my parts start with P.