Custom Format

[Guest]

I am trying to do a custom format so that what is entered in the cell will automatically enter a dash.

Example:
T1-0504

All they will enter will be:
T10504

What format do I need to enter.

Thanks

 

[Frank Kabel]

Hi
this is not possible as you don't enter a value. But if the user enter
10504
(without a leading character) you could use the custom format
"T"0-0000

 

[Dave Peterson]

And if your first letter is not always T, you could have a worksheet event
handle the conversion--more than just formatting, the actual value changes:

If you want to try it, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this in the codewindow:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a99")) Is Nothing Then Exit Sub


On Error GoTo errHandler:
With Target
If Len(.Value) = 6 Then
If IsNumeric(Mid(.Value, 2)) Then
Application.EnableEvents = False
.Value = UCase(Left(.Value, 2)) & "-" & Mid(.Value, 3)
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


Change that a1:a99 to just the range of cells you want modified.

Then back to excel to try it out.