Number formatting only works on numbers.

But you could use a little macro waiting for you to make a change to a cell.

If that sounds ok, right click on the worksheet tab that should have this
behavior and select view code. Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

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

'XX:XXXX:XX
On Error GoTo ErrHandler:

With Target
If .NumberFormat = "@" Then
If Len(.Value) = 8 Then
'do it!
Application.EnableEvents = False
.Value = Left(.Value, 2) & ":" & Mid(.Value, 3, 4) _
& ":" & Right(.Value, 2)
End If
End If
End With

ErrHandler:
Application.EnableEvents = True

End Sub

The code looks to make sure that your cell is formatted as Text and that it's
exactly 8 characters long.

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

mustard wrote:

I would like to have text custom formatted so that if a 8 character
words is typed, it is automatically broken up like this XX:XXXX:XX.
This works just fine with numbers with a ##\:####\:## custom format,
but it will not work for text. Any suggestions?

--
mustard
------------------------------------------------------------------------
mustard's Profile: http://www.excelforum.com/member.php...o&userid=20017
View this thread: http://www.excelforum.com/showthread...hreadid=346023

--

Dave Peterson