Better still, the macro can auto-correct on the fly. Say data is being
entered into column A. Put the following event macro in the worksheet code
area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set A = Range("A:A")
If Intersect(t, A) Is Nothing Then Exit Sub
v = t.Value
If Len(v) = 11 Then Exit Sub
If Len(v) = 10 Then
Application.EnableEvents = False
t.Value = Left(v, 8) & "-" & Right(v, 2)
Application.EnableEvents = True
End If
End Sub

So if the length of the entry is 11, it is left alone. If the length is 10,
then the dash is inserted.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

--
Gary''s Student - gsnu201003


"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.
--
TIA, Brad E.