Not a Custom format but how about event code that formats when you enter the
data?

Same as Bob's helper cell formula except in place.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
On Error GoTo endit
Application.EnableEvents = False
If Len(Target) = 10 Then
Target.Value = Left(Target, 2) & "-" _
& Mid(Target, 3, 4) & "-" _
& Mid(Target, 7, 1) & "-" _
& Right(Target, 3)
End If
endit:
Application.EnableEvents = True
End If
End Sub

Right-click on the sheet tab and "View Code". Copy/paste into that module.
Edit to suit then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Fri, 20 Feb 2009 11:33:02 -0800, May47
wrote:

does anyone know how to create a custom format in Excel 2007 that will all
you to enter this: 123456B789 and it automatically changes it to
12-3456-B-789

The letter can change so the mask should be able to accomodate that. I
found one that will work if the letter is always the same - but it won't work
if it's anything other than a B.