☰

Excel-Format „Buchstaben plus Zahlen“

tag-icon • tag-icon microsoft-excel worksheet-function
Excel-Format „Buchstaben plus Zahlen“

Ich habe ein Problem damit, herauszufinden, wie ich einer bestimmten Spalte ein bestimmtes Format aufzwingen kann.

Ich beschäftige mich hier mit einer Kalkulationstabelle, die ich zur Bestandsverfolgung verwende. Sie wird von mehreren Personen verwendet und daher sind die Standortformate inkonsistent.

(zB: A1, A01, A-1, A-01)

Das macht das Sortieren etwas mühsam, da A1 über A10 steht, statt A1,A2. Ich möchte das Problem beheben, sodass unabhängig davon, was eingegeben wird, es in das Format Buchstabe+2-stellige Zahl (A01) korrigiert wird.

Dank im Voraus

Antwort1

Dies ist innerhalb der Eingabespalte nicht möglich.

  • Sie könnten hinzufügen einzusätzliche (Hilfs-)SpalteDas enthält die Formel, die die Teile Ihrer Zeichenfolge aufspaltet, jedes entsprechend formatiert und sie ordentlich zusammenfügt.
  • Wenn alle Ihre Zeichenfolgen mit A beginnen, können Sie einfach jedem sagen,lass das A fallen(und den Bindestrich) und geben Sie einfach eine Zahl ein (aber vielleicht haben Sie gerade ein Beispiel gegeben)
  • Du könntest habenzweiSpalten, eine für den Alphateil und eine für die Zahl
  • Sie könnten schreibenMakrodas alles analysiert, was sie eingeben, und die Sortierung vornimmt. Aber erfahrungsgemäß gibt es kein Makro auf der Welt, das mit allen absurden Variationen umgehen kann, die sich ein Benutzer möglicherweise ausdenken könnte – er wird immer eine Variation finden, die es kaputt macht.

Antwort2

Sie können auch die Datenüberprüfung mit verständlichen Eingabeaufforderungen und Meldungen verwenden. Die Datenüberprüfungsformel (vorausgesetzt, Sie möchten Großbuchstaben) lautet:

=AND(CODE(A1)>=65,CODE(A1)<=90,CODE(MID(A1,2,1))>=48,CODE(MID(A1,2,1))<=57,CODE(MID(A1,3,1))>=48,CODE(MID(A1,3,1))<=57)

Antwort3

Platzieren Sie das folgende Ereignismakro im Codebereich des Arbeitsblatts:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B As Range, s As String
    Set B = Range("B:B")
    If Intersect(Target, B) Is Nothing Then Exit Sub
    s = Target.Value
    If Len(s) <> 3 Then GoTo fixit
    If Not Left(s, 1) Like "[A-Z]" Then GoTo fixit
    If Not IsNumeric(Mid(s, 2, 2)) Then GoTo fixit
    Exit Sub
fixit:
    Application.EnableEvents = False
        Target.Value = "A00"
    Application.EnableEvents = True
End Sub

Dieses Beispielmakro überwacht und korrigiert Einträge in der SpalteB. Wenn die Einträge gültig sind, werden sie unverändert gelassen. Wenn ein Eintrag ungültig ist, wird er ersetzt durchA00.

Antwort4

Wenn Sie nur den Wert prüfen möchten und die Prüflogik nicht zu komplex ist, können Sie dies mit der standardmäßigen Excel-Datenüberprüfung tun. Da Sie den Wert jedoch neu formatieren möchten, damit er korrekt ist, müssen Sie VBA oder Visual Studio Tools for Office verwenden. Das Folgende ist ein einfaches VBA-Makro, das das tut, was Sie möchten.

Die Magie geschieht, wenn dieArbeitsblatt_ÄndernEreignis ausgelöst wird. Für das Beispiel gehe ich davon aus, dass Sie in der ersten Spalte einzelne Werte eingeben.Richtige TeilenummerDie Funktion übernimmt die Schwerstarbeit. Sie analysiert den eingegebenen Wert und gibt einen korrekt formatierten Wert zurück oder nichts, um anzuzeigen, dass ein Validierungsfehler aufgetreten ist (sie prüft nur die maximale Länge). Die Verarbeitung funktioniert mit Ihrem Beispiel, ist aber für die reale Welt wahrscheinlich zu einfach. Sie könnte leicht mit regulären Ausdrücken oder anderen ähnlich leistungsstarken Verarbeitungsmethoden erweitert werden.

Der Ereignishandler fährt fort, indem er entweder die Zelle mit der korrekt formatierten Teilenummer aktualisiert oder den vom Benutzer eingegebenen Wert fett und rot darstellt, um einen Fehler anzuzeigen.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim NewValue As String

    If (Target.Column = 1 And Target.Count = 1) Then
        Application.EnableEvents = False
        NewValue = CorrectPartNo(Target.Value)
        If (Len(NewValue) = 0) Then
            Target.Font.Color = vbRed
            Target.Font.Bold = True
        Else
            Target.Value = NewValue
        End If
        Application.EnableEvents = True
    End If
End Sub

Function CorrectPartNo(PartNo As String)
    Dim StartPartNo As String
    Dim EndPartNo As String
    Dim EndPartNoPosition As Integer

    StartPartNo = Left(PartNo, 1)
    If (Mid(PartNo, 2, 1) = "-") Then
        If (Len(PartNo) > 4) Then
            CorrectPartNo = ""
            Exit Function
        Else
            EndPartNoPosition = 3
        End If
    Else
        If (Len(PartNo) > 3) Then
            CorrectPartNo = ""
            Exit Function
        Else
            EndPartNoPosition = 2
        End If
    End If
    EndPartNo = Right("0" + Mid(PartNo, EndPartNoPosition), 2)

    CorrectPartNo = StartPartNo + EndPartNo
End Function

verwandte Informationen