Custom number format for displaying MAC addresses?

[skiraly017]

I'm trying to create an Excel sheet to display MAC addresses in the following format -

00:00:00:00:00:00

However. the characters can be either letters or numbers. I've modified the "hh:mm:ss" template to accomodate the format I need, but it will not recognize letters for the obvious reason. Is there a wildcard character that I could use in this format or has anyone created a format that would meet my needs? If I can pull this off, it could mean a promotion. Thanks in advance for any and all help.

 

[lenze]

Remove this line from the code

If Target.Column <> 1 Then Exit Sub 'column of interest

CAUTION!!!
Now all cells from row two down will be formatted that way regardless of what is entered. For instance "This is a test" becomes " Th:is:s :is:st"

You probably need to identify the cells where you want this format applied and name them. You can name non-contiguous cells by holding down the CTRL key while selecting.

Once they are named (say myRange ) insert an intersect statement in the code

Example
If Intersect(Target, Range("myRange")) Is Nothing Then Exit Sub

HTH

lenze

 

[skiraly017]

Back again as the project I needed this for was suddenly dropped in my lap again. I modified the script Lenze provided on page one of this thread to meet my needs. The problem I havenow is copying and pasting. Here's my situation -

I am receiving raw data feed that list MAC addresses like this -

111111111111
222222222222
333333333333

If I try to copy and paste them from the data feed Excel sheet into my pre-formatted MAC address sheet, the pasted numbers do not format into the desired 11-11-11-11-11-11. However, if I do them one at a time they transfer and format just fine. Any ideas/suggestions?

TIA

 

[skiraly017]

Just wanted to give this a bump since it got buried quickly. Thanks for the help.