Did you try Ron's code? I think it will work as you expect.

--
Rick (MVP - Excel)


"streksh" wrote in message
...
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....