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.

There is no mask-edit in Excel for text -- your best bet would be to enter
the value elsewhere and reference that cell in a formula, like:
=LEFT(Z2,2)&"-"&MID(Z2,3,4)&"-"&MID(Z2,7,1)&"-"&RIGHT(Z2,3)

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

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.


Excel allows custom *number* formats. What you are entering is text. I believe
this would require some programming.

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.