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