formatting cells

[Adrian]

I am trying to format a column so that it keeps track of
computer hardware mac addresses.

-Each cell must be limited to 17 characters only.
-Cells must have 5 visible permanent colons so that users
may only enter the remaining 12 letters/numbers.
-cells must be restricted to entry of letters and numbers
(no special characters).
-users must be presented with an error message if they try
to leave the cell without entering 12 letters/numbers.
-column must not accept duplicate mac addresses.


Can anyone give me some direction on how to do this?

 

[David McRitchie]

Hi Adrian,
What kind of identification can have colons) in random
locations ? If there are specific locations for the colons
you can add those tests to your User Defined Function (UDF).

All of what you are asking for is in Cell Validation, which
you can read in detail about how to set it up at Debra Dalgleish's
DataVa101 and DataVa1021, Data Validation lists,
http://www.contextures.com/xlDataVal01.html

and additonal examples of formulas at
Validate / Validation
http://www.mvps.org/dmcritchie/excel/validation.htm.

As far as I know you will have to use a User Defined Function
to verify only letters, digits

A validation formula generates True or False
you can test the formula on your worksheet before you place
it into validation
=AND(LEN(A1)=17,adrian_c5ld($A1),COUNTIF($A:$A,A1)=1)

Select column A, with cell A1 as the Active cell,
Data (menu), Validation, custom in top box, formula in the other

the User Defined Function used above, must be installed, if not familar with
installing macros and functions see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Function Adrian_C5LD(arg1 As String) As Boolean
Dim stripped As String
Dim i As Long
Dim str As String
str = UCase(arg1)
If Len(str) <> 17 Then GoTo bad
stripped = Replace(str, ":", "")
If Len(stripped) <> 12 Then GoTo bad
For i = 1 To Len(stripped)
If Mid(str, i, 1) < "0" Or Mid(str, i, 1) > "Z" Then GoTo bad
If Mid(str, i, 1) > "9" And Mid(str, i, 1) > "a" Then GoTo bad
Next i
Adrian_C5LD = 1 ' True good
Exit Function
bad: Adrian_C5LD = 0
End Function

Note Validation will not prevent pasting a value into a cell, in fact
pasting into a cell will wipe out validation and conditional formatting
for that cell as would using the format painter.