I am sure this is simple, but I just can't figure it out. I want to create a
custom format for a cell with both text and numbers. I want to be able to
type in something like "22111AA" and have it display as "22-111-AA". Can
anyone help? THX.

Number formatting will only work on numbers.

You can either use another cell with a formula in it:

=left(a1,2)&"-"&mid(a2,3,3)&"-"&mid(a2,6,2)

Or you could have an event macro...

If you want to try, right click on the worksheet tab that should have this
behavior. Select view code and paste this into the codewindow:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
'one cell at a time
If .Cells.Count 1 Then Exit Sub
'only column A
If Intersect(Me.Range("A:A"), .Cells) Is Nothing Then Exit Sub

'gotta be 7 characters
If Len(.Value) < 7 Then Exit Sub

On Error GoTo ErrHandler:
Application.EnableEvents = False
.Value = Left(.Value, 2) & "-" _
& Mid(.Value, 3, 3) & "-" & Mid(.Value, 6, 2)
End With

ErrHandler:
Application.EnableEvents = True
End Sub

I check for entries in column A. Change that to the range you want to use.

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


tkeith wrote:

I am sure this is simple, but I just can't figure it out. I want to create a
custom format for a cell with both text and numbers. I want to be able to
type in something like "22111AA" and have it display as "22-111-AA". Can
anyone help? THX.

--

Dave Peterson