|As far as I know it can't be done with Custom Formatting. Excel never claimed to be a text editor, so it's text formatting options are limited. |
I can think of 2 work-arounds:
1 - Put your 10 character string in A1 and put this in another:
Note 1: You might have to use the 00-00-00-00-00 custom format on the original data to retain the leading zeros.
Note 2: You might have to enter the single quote text character before the string if there are too many leading zeros in the data.
2 - You could use a Worksheet_Change macro if you want to keep the data in the same cell as entered, but you might have to same issue with leading zeros as in my other suggestion.
For example, if the data will be entered in column A, right click the sheet tab and paste in this code.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
MyMAC = Target
NewMAC = Left(MyMAC, 2) & "-" _
& Mid(MyMAC, 3, 2) & "-" _
& Mid(MyMAC, 5, 2) & "-" _
& Mid(MyMAC, 7, 2) & "-" _
& Right(MyMAC, 2)
Target = NewMAC
Application.EnableEvents = True
Note: This code does not check to see if the data is 10 characters or anything like that, so it's going to try and format whatever you type into Column A. That can be fixed, but it takes a little more code.