Angenommen, Sie haben ein Arbeitsblatt mit vielen Produktcodes in Spalte A.

Diese Codes haben das Format A4, B12, AD4 usw. Aufgrund einer Änderung in der Arbeitsweise Ihres Unternehmens werden Sie angewiesen, alle Produktcodes so zu ändern, dass sie einen Bindestrich zwischen den Buchstaben und den Zahlen enthalten.

Es gibt verschiedene Möglichkeiten, wie Sie diese Aufgabe ausführen können. Wenn die Struktur Ihrer Produktcodes konsistent ist, ist das Einfügen der Striche ein Kinderspiel.

Wenn zum Beispiel immer ein einzelner Buchstabe gefolgt von Zahlen steht, können Sie eine Formel wie die folgende verwenden:

=LEFT(A1,1) & "-" & RIGHT(A1,LEN(A1)-1)

Die Chancen stehen gut, dass Ihre Daten nicht strukturiert werden. Dies bedeutet, dass Sie ein oder zwei Buchstaben gefolgt von bis zu drei Ziffern haben können. Somit wären sowohl A4 als auch QD284 gültige Produktcodes. In diesem Fall erfordert eine Lösungsformel etwas mehr Kreativität.

Eine Möglichkeit, damit umzugehen, ist eine Array-Formel. Betrachten Sie die folgende Formel:

=REPLACE(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0),0,"-")

Wenn sich die Werte in A1-A10 befinden, können Sie diese Formel in B1 einfügen und dann in die Spalte kopieren. Da es sich um eine Array-Formel handelt, muss sie durch Drücken von Strg + Umschalt + Eingabetaste eingegeben werden. Die Formel findet die Position der ersten Zahl in der Zelle und fügt einen Bindestrich davor ein.

Angenommen, die Zelle A1 enthält BR27. Der innerste Teil der Formel, INDIREKT („1: 100“), konvertiert den Text 1: 100 in einen Bereich. Dies wird verwendet, damit das Einfügen oder Löschen von Zeilen die Formel nicht beeinflusst. Der nächste Teil der Formel, ROW (INDIRECT („1: 100“)), erstellt im Wesentlichen ein Array der Werte 1-100: 1,2,3, …​, 99,100. Dies wird verwendet, um auf jedes Zeichen in der Zelle einzuwirken.

Der nächste Teil, MID (A1, ROW (INDIRECT („1: 100“)), 1), bezieht sich auf jedes einzelne Zeichen in der Zeichenfolge. Dies führt zu dem Array: „B“, „R“, „2“ und „7“. Multiplizieren Sie das Array mit 1 (der nächste Teil der Formel)

führt dazu, dass jedes einzelne Zeichen in eine Zahl umgewandelt wird. Wenn das Zeichen keine Zahl ist, führt diese Konvertierung zu einem Fehler. Im Fall der zu konvertierenden Zeichenfolge (BR27) führt dies zu: #VALUE, #VALUE, 2 und 7.

Der nächste Schritt besteht darin, die ISERROR-Funktion auf die Ergebnisse der Multiplikation anzuwenden. Dies konvertiert die Fehler in TRUE und die Nichtfehler in FALSE, was TRUE, TRUE, FALSE und FALSE ergibt. Die MATCH-Funktion sucht im Array der Werte TRUE und FALSE nach einer genauen Übereinstimmung von FALSE. In diesem Beispiel gibt die MATCH-Funktion die Nummer 3 zurück, da sich der erste FALSE-Wert an der dritten Position des Arrays befindet. Zu diesem Zeitpunkt kennen wir im Wesentlichen die Position der ersten Zahl in der Zelle.

Die letzte Funktion ist REPLACE, mit der der Bindestrich ab dem dritten Zeichen tatsächlich in die Quellzeichenfolge eingefügt wird.

Wie Sie sehen können, kann die Entschlüsselung der Formel zum Durchführen der Transformation etwas entmutigend sein. Für diejenigen, die dazu neigen, ist es möglicherweise einfacher, nur eine benutzerdefinierte Funktion zu erstellen. Das folgende Makro ist ein Beispiel für ein Makro, das eine Zeichenfolge mit dem Bindestrich an der richtigen Stelle zurückgibt:

Function DashIn(myText As String)

Dim i As Integer     Dim myCharCode As Integer     Dim myLength As Integer

Application.Volatile     myLength = Len(myText)

For i = 1 To myLength         myCharCode = Asc(Mid(myText, i, 1))

If myCharCode >= 48 And myCharCode <= 57 Then             Exit For         End If     Next i     If i = 1 Or i > myLength Then         DashIn = myText     Else         DashIn = Left(myText, i - 1) & "-" _           & Mid(myText, i, myLength - 1)

End If End Function

Das Makro untersucht jedes Zeichen in der ursprünglichen Zeichenfolge und fügt an dieser Stelle einen Bindestrich ein, wenn es das erste numerische Zeichen findet. Sie würden die Funktion folgendermaßen verwenden:

=DashIn(A1)

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (10182) gilt für Microsoft Excel 2007 und 2010. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Einfügen von Strichen zwischen Buchstaben und Zahlen.