Marty hat ein Arbeitsblatt mit einer langen Reihe von Teilenummern in Spalte A.

Diese bestehen aus Buchstaben und Zahlen wie A123BC, AB123C usw. Marty möchte die Daten in drei Spalten aufteilen, sodass der Text vor den Zahlen in einer Spalte, die Zahlen in der zweiten Spalte und der Text nach den Zahlen in der Spalte stehen dritte.

Der Faktor, der die Aufteilung der Teilenummer in Segmente erschwert, besteht darin, dass für jede Komponente der kombinierten Teilenummer keine festgelegte Länge vorhanden ist. Wenn die Komponenten Standardlängen haben, können Sie die Funktion Text in Spalten in Excel verwenden. Da dies nicht der Fall ist und es kein Trennzeichen zwischen den Komponenten gibt, ist dieser potenzielle Weg für eine Lösung geschlossen.

Wenn Sie Formeln verwenden möchten, um die Teilenummern auseinander zu ziehen, benötigen Sie drei davon, eine für jede Komponente, die Sie extrahieren möchten.

Angenommen, die Teilenummern folgen dem angegebenen Muster (Text, Ziffern, Text) und die erste Teilenummer befindet sich in Zelle A1. In Zelle B1 können Sie Folgendes verwenden:

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

Dies muss als Array-Formel eingegeben werden. Dies bedeutet, dass Sie sie mit Strg + Umschalt + Eingabetaste eingeben müssen. Die Formel findet die erste numerische Ziffer in der Teilenummer und gibt dann alles vor dieser Ziffer zurück.

Es funktioniert mit jeder Teilenummer, die nicht länger als 100 Zeichen ist.

Um die zweite Komponente der Teilenummer zu extrahieren, können Sie die folgende Formel in Zelle C1 einfügen:

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

Auch dies ist eine einzelne Formel, die als Array-Formel (Strg + Umschalt + Eingabetaste) eingegeben werden muss, damit sie für jedes Zeichen in der ursprünglichen Teilenummer verwendet werden kann. Es untersucht die Teilenummer, bestimmt den Anfangspunkt der Ziffern und extrahiert dann alle diese Ziffern. Es wird eine Textzeichenfolge zurückgegeben, obwohl diese Zeichenfolge aus Ziffern besteht. Wenn Sie möchten, dass es tatsächlich als Zahl behandelt wird (wodurch natürlich alle führenden Nullen entfernt werden), müssen Sie die gesamte Formel in eine Wertfunktion einschließen, wie hier gezeigt:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1))))

Um die letzte Komponente der Teilenummer zu erhalten, müssen Sie die folgende Formel verwenden, die erneut als Array-Formel eingegeben wird:

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0)+COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)),100)

Obwohl dieser Ansatz sehr gut funktioniert, sind Array-Formeln bekanntermaßen rechenintensiv, insbesondere wenn Sie viele Formeln in Ihrem Arbeitsblatt haben. Wenn Sie tausend Teilenummern auseinander ziehen müssen, bedeutet dies, dass Sie am Ende 3.000 Array-Formeln haben, die bei der Neuberechnung sehr, sehr langsam sein können.

Wenn Sie sich in dieser Situation befinden, möchten Sie möglicherweise ein Makro verwenden, um die Teilenummern tatsächlich auseinander zu ziehen. Das folgende Makro sollte für Teilenummern funktionieren, die dem bereits beschriebenen Muster aus Text, Ziffern und Text folgen.

Sub Split1()

Dim C As Range     Dim sNew As New     Dim i As Integer          For Each C In Selection         sNew = ""

i = 1

' Get first part, which is text         Do While IsNumeric(Mid(C, i, 1)) = False             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 1).Value = sNew

' Pull next part, which should be digits         sNew = ""

Do While IsNumeric(Mid(C, i, 1)) = True             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 2).Value = sNew

' Use rest of original cell         sNew = Mid(C, i, Len(C))

C.Offset(0, 3).Value = sNew     Next C End Sub

Um das Makro zu verwenden, treffen Sie einfach eine Auswahl von Teilenummern und führen Sie es aus. Das Makro verwendet das Konzept, nach Änderungen zwischen numerischen / nicht numerischen Werten in einer Zeichenfolge in der Zelle zu suchen. Wenn eine dieser Grenzen erreicht ist, wird der Teil der ursprünglichen Zeichenfolge vor der Grenze in eine neue Zelle eingefügt. Dieses Konzept kann etwas verkürzt werden, wie im folgenden Beispiel dargestellt.

Sub Split2()

Dim C As Range     Dim j As Integer     Dim k As Integer

For Each C In Selection         j = 1         Do While Not (IsNumeric(Mid(C.Value, j, 1))) And j <= Len(C)

j = j + 1         Loop         k = j

Do While IsNumeric(Mid(C.Value, k, 1)) And k <= Len(C)

k = k + 1         Loop

C.Offset(0, 1) = Left(C, j - 1)

C.Offset(0, 2) = Mid(C, j, k - j)

C.Offset(0, 3) = Mid(C, k, Len(C) - (k - 1))

Next C End Sub

Der Unterschied zwischen dieser Version des Makros und der vorherigen besteht natürlich darin, dass diese Version die ursprüngliche Zelle durchläuft und die Grenzen auf einmal bestimmt. Wenn sie bekannt sind, werden die Komponenten der ursprünglichen Teilenummer in die Zellen gestopft.

Ein interessanter Ansatz zum Auseinanderziehen der Teilenummern besteht darin, einige kurze benutzerdefinierte Funktionen zu verwenden, die bestimmen, wo die Grenzen zwischen den Komponenten liegen. Betrachten Sie die folgenden zwei Funktionen:

Function pNumber(X)

i = 1     Do Until Mid(X, i, 1) Like "#": i = i + 1: Loop     pNumber = i End Function
Function pAlpha(X)

X = UCase(X)

i = pNumber(X)

Do Until Mid(X, i, 1) Like "[A-Z]": i = i + 1: Loop     pAlpha = i End Function

Diese sind viel kürzer als die vorherigen Makros und geben lediglich die Grenze zurück, an der die Zahlen beginnen (im Fall von pNumber), und die Grenze, an der die zweite Textgruppe beginnt (im Fall von pAlpha). Um die Funktionen zu verwenden, verwenden Sie die folgenden drei Formeln, um die erste, zweite und dritte Komponente der ursprünglichen Teilenummer zurückzugeben:

=MID(A1,1,pNumber(A1)-1)

=MID(A1,pNumber(A1),pAlpha(A1)-pNumber(A1))

=MID(A1,pAlpha(A1),LEN(A1)-pAlpha(A1)+1)

_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 (10369) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tippes für die ältere Menüoberfläche von Excel finden Sie hier:

Aufteilen von Teilenummern variabler Länge.