CUSTOM TEXT FORMATTING???

  • Thread starter Thread starter JLC
  • Start date Start date
J

JLC

My cell has 8 mixed characters of numbers and letters. I
need to have a hyphen "-" after the first 4 characters in
a column. Can I make a custom text format so no matter
what I enter a hyphen is automatically inserted? How do I
do that?

JLC
 
Hi
this could only be done with VBA, using an event procedure (as you have
text and numbers in your cell). e.g. put the following code in your
worksheet module (not in a standard module):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If len(.Value) = 8 Then
.value = left(.value,4) & "-" & right(.value,4)
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
Back
Top