On Sep 10, 8:54*pm, Alan wrote:
* *You can use the Worksheet_SelectionChange event (i.e., when user
goes to another cell after entry) to trigger this, and use string
functions to parse the string.

Here is some quick, example code that seems to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
* * Const LeftNumChars = 5
* * Dim EnteredString As String
* * EnteredString = ""
* * ' Check that the user modified a single cell
* * If Target.Cells.Count = 1 Then
* * * * ' Check that the cell is in Column of interest (e.g., "D" = 4)
* * * * If Target.Column = 4 Then
* * * * * * ' Get the value of the changed cell and remove leading/
trailing blanks
* * * * * * EnteredString = Trim$(ActiveSheet.Cells(Target.Row - 1,
Target.Column).Value)
* * * * * * ' Make sure there is not already a dash in the cell
* * * * * * If Not InStr(EnteredString, "-") Then
* * * * * * * * ' Check the length of the entered string
* * * * * * * * If Len(EnteredString) LeftNumChars Then
* * * * * * * * * * 'Get the right and left pieces of the string, and
insert the dash between them
* * * * * * * * * * EnteredString = Left$(EnteredString, LeftNumChars)
& "-" & _
* * * * * * * * * * * * * * Right$(EnteredString, Len(EnteredString) -
LeftNumChars)
* * * * * * * * * * ' Write this value to the cell
* * * * * * * * * * ActiveSheet.Cells(Target.Row - 1,
Target.Column).Value = EnteredString
* * * * * * * * End If
* * * * * * End If
* * * * End If
* * End If
End Sub

Make sure you put this in a module associated with the worksheet of
interest. *In the VBE Project window, right click on the worksheet to
which you wish to apply this code and then click "View Code" to get
started. *Seehttp://www.dailydoseofexcel.com/archives/2004/05/03/beginning-vba-eve....

Also, seehttp://www.techonthenet.com/excel/formulas/index_vba.phpif
you are not familiar with the string functions.

HTH, Alan

Thanks to everyone for all of the help!! All of the replies were
extremely helpful. I did find a minor bug...if i scroll back up
through the cells in column D, it keeps adding the "-" over and over
and eventually errors out. Is there a way to reset the values or
inspect for an existing dash so that it doesn't do that? I'm not sure
how many users would actually do that, but I don't like to leave a
known issue....