Custom Format text XX:XXXX:XX

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?

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

Thanks, Dave, but I'm still a bit confused. I have pasted that text into the the source of that page, but how do I call it up? It is not included in my list of available macros...