Ich weiss der Thread ist uralt, aber da ich das gleiche Problem hatte. Eine einfache Lösung mit Boardmitteln habe ich so nicht gefunden (Benutzerdefiniertes Format o.ä.). Die Lösung mit Teilstrings war mir zu unflexibel, weil verschiedene Endgeräte die MAC Adresse in unterschiedlichen Formaten angeben.
00:11:22:33:44:55
00-11-22-33-44-55
0011.2233.4455
001122.334455
Habe das jetzt in VBA gelöst. Hatte damit vorher keine Berührungspunkte und aktivierte Makros können ja ein Sicherheitsrisiko sein, aber gut.
Unzulässige Zeichen werden rausgefiltert.
Ergebnis sind immer Großbuchstaben und als Trennzeichen wird : verwendet.
Ist aber natürlich unten leicht anpassbar, wer z.B. einheitlich den - als Trennzeichen haben möchte.
Aufrufen des Excel VBA Editors mit Alt+F11
Extras--> Verweise --> Microsoft VBScript Regular Expressions 5.5 anhaken und mit OK bestätigen
Dann das Arbeitsplatt oder die Tabelle auswählen, und in der Befehlsleiste unter Einfügen ein Modul hinzufügen und den untenstehenden Code einfügen.
Variante 1 als Makro Sub (Achtung: Originaldaten werden verändert, bei fehlerhafter Eingabe bleibt der Zellinhalt bestehen und die Zelle wird rot):
Code:
Option Explicit
Sub FormatMacAddress()
Dim Zelle As Range
For Each Zelle In Selection
If Not Zelle.Value = "" Then
Dim sText As String
sText = Zelle.Value
sText = StrConv(sText, vbUpperCase)
Dim regEx2 As New RegExp
Dim strPattern2 As String: strPattern2 = "[^0-9A-F]"
If strPattern2 <> "" Then
With regEx2
.Global = True
.MultiLine = False
.IgnoreCase = True
.Pattern = strPattern2
End With
If regEx2.Test(sText) Then
sText = regEx2.Replace(sText, "")
Else
End If
End If
If Len(sText) < 12 Then
'MsgBox "Error: zuwenige oder unzulässige Zeichen in Zelle " & Zelle.Address & " ! Muss: 12 + Trennzeichen"
Zelle.Interior.ColorIndex = 3
ElseIf Len(sText) > 12 Then
'MsgBox "Error: zuviele oder unzulässige Zeichen in Zelle " & Zelle.Address & "! Muss: 12 + Trennzeichen"
Zelle.Interior.ColorIndex = 3
ElseIf Len(sText) = 12 Then
If Zelle.Interior.ColorIndex = 3 Then
Zelle.Interior.ColorIndex = 0
End If
Zelle.Value = Format(sText, "@@:@@:@@:@@:@@:@@")
Else
MsgBox "Error!"
Zelle.Interior.ColorIndex = 3
End If
End If
Next Zelle
End Sub
Und das ganze als Funktion, wenn die Originalzelle nicht bearbeitet werden soll:
Code:
Option Explicit
Option Base 1
Function FormatMAC(varMAC)
Dim sText As String
sText = varMAC
sText = StrConv(sText, vbUpperCase)
Dim regEx2 As New RegExp
Dim strPattern2 As String: strPattern2 = "[^0-9A-F]"
If strPattern2 <> "" Then
With regEx2
.Global = True
.MultiLine = False
.IgnoreCase = True
.Pattern = strPattern2
End With
If regEx2.TEST(sText) Then
sText = regEx2.Replace(sText, "")
Else
End If
End If
If Len(sText) < 12 Then
FormatMAC = "Error: zuwenige Zeichen oder unzulässige Zeichen enthalten! Muss: 12 + Trennzeichen - " & sText
Exit Function
ElseIf Len(sText) > 12 Then
FormatMAC = "Error: zuviele Zeichen! Muss: 12 + Trennzeichen - " & sText
Exit Function
End If
FormatMAC = Format(sText, "@@:@@:@@:@@:@@:@@")
End Function
Und hier nochmals als Funktion, die einem die Position des ersten Fehlers mitteilt:
Würde ich aber nicht nutzen, zu viel Ballast, war aber mein erster Entwurf, bevor ich zu den o.g. Lösungen gekommen bin.
Code:
Option Explicit
Option Base 1
Function FormatMAC(varMAC)
Dim sText As String
sText = varMAC
sText = StrConv(sText, vbUpperCase)
' Lösung vor dem RexExp Ansatz !
Dim TOsChars As String
Dim sChars As Variant
Dim nCount As Integer
Dim i As Integer
TOsChars = "_ - . : , ; "
sChars = Split(TOsChars, " ")
nCount = UBound(sChars)
For i = 0 To nCount - 1
sText = Replace(sText, sChars(i), "")
Next i
' Lösung vor RegExp für Ersetzen von Zeichen
Dim ii As Integer
ii = 1
Dim iii As Integer
iii = 1
Dim errortxt As String
errortxt = ""
Dim strSonderzeichen As String
strSonderzeichen = "ABCDEF0123456789"
For ii = 1 To Len(sText)
For iii = 1 To Len(strSonderzeichen)
If Mid(sText, ii, 1) = Mid(strSonderzeichen, iii, 1) Then
Exit For
Else
If iii = Len(strSonderzeichen) Then
errortxt = "Error: Unzulässiger Wert an Position " & ii & " von links"
FormatMAC = errortxt
Exit Function
Else
End If
End If
Next iii
Next ii
' Lösung vor RegExp für Ersetzen von Zeichen
If Len(sText) = 12 Then
FormatMAC = Format(sText, "@@:@@:@@:@@:@@:@@")
ElseIf Len(sText) < 12 Then
FormatMAC = "Error: zuwenige Zeichen!"
ElseIf Len(sText) > 12 Then
FormatMAC = "Error: zuviele Zeichen!"
Else
FormatMAC = "Error!"
End If
End Function
