Tom hat ein Arbeitsblatt, das ungefähr 20.000 Zellen mit Textdaten enthält. Aus diesen Zellen muss er ein bestimmtes Textmuster extrahieren. Das Muster ist – #, wobei jedes # eine Ziffer ist. Dieses Muster erscheint nicht an einer festgelegten Stelle in jeder Zelle. Tom fragt sich, ob es eine Möglichkeit gibt, die gewünschten Informationen zu extrahieren.

Es gibt verschiedene Möglichkeiten, wie Sie dieses Problem angehen können. Die richtige Lösung für Ihre Anforderungen hängt von den Merkmalen der Daten ab, mit denen Sie arbeiten. Wenn Sie wissen, dass der einzige Ort in Ihren Daten, an dem Sie einen Bindestrich haben, innerhalb Ihres Musters liegt, können Sie das Vorhandensein des Bindestrichs mithilfe einer Formel wie der folgenden abtasten:

=MID(A1,FIND("-",A1)-2,8)

Dies findet den Bindestrich und greift dann nach den beiden Zeichen links vom Bindestrich. Dies funktioniert offensichtlich nicht, wenn an anderen Stellen im Text Bindestriche vorhanden sind oder wenn es möglich ist, „Muster“ zu haben, die nicht Ziffern enthalten (z. B. 12-34B32), und Sie diese ausschließen möchten. In diesem Fall benötigen Sie eine viel komplexere Formel:

=IF(ISERROR(INT(MID(A1, FIND("-", A1, 1)-2, 2)) & INT(MID( A1, FIND("-", A1, 1)+1, 5))), "", MID(A1, FIND("-", A1)-2, 8))

Dies umfasst eine Fehlerprüfungskomponente, die herausfindet, ob die Zeichen unmittelbar vor dem Bindestrich und unmittelbar nach dem Bindestrich etwas anderes als Ziffern enthalten. Wenn dies der Fall ist, wird nichts zurückgegeben.

Das einzige, was diese formelhaften Ansätze nicht tun, ist, Situationen zu behandeln, in denen das Muster in derselben Zelle mehr als einmal auftreten kann. In diesem Fall ist ein Makro der beste Ansatz. Im Folgenden werden die gültigen Muster extrahiert und in ein neues Arbeitsblatt mit dem Namen „Ergebnisse“ eingefügt.

Sub ExtractPattern()

On Error Resume Next     Set SourceSheet = ActiveSheet     Set TargetSheet = ActiveWorkbook.Sheets("Results")

If Err = 0 Then         Worksheets("Results").Delete     End If     Worksheets.Add     ActiveSheet.Name = "Results"

Set TargetSheet = ActiveSheet     Cells(1, 1).Value = "Found Codes"

Cells(1, 1).Font.Bold = True     iTargetRow = 2

SourceSheet.Select     Selection.SpecialCells(xlCellTypeLastCell).Select     Range(Selection, Cells(1)).Select

For Each c In Selection.Cells         If c.Value Like "##-#####" Then             sRaw = c.Value             iPos = InStr(sRaw, "-")

Do While iPos > 0                 If iPos < 3 Then                     sRaw = "  " & sRaw                     iPos = iPos + 2                 End If                 sTemp = Mid(sRaw, iPos - 2, 8)

sRaw = Mid(sRaw, iPos + 6, Len(sRaw))

If sTemp Like "##-#####" Then                     TargetSheet.Cells(iTargetRow, 1) = sTemp                     iTargetRow = iTargetRow + 1                 Else                     sRaw = Mid(sTemp, 4, 5) & sRaw                 End If                 iPos = InStr(sRaw, "-")

Loop         End If     Next c End Sub

Beachten Sie, dass das Makro die Like-Funktion an zwei Stellen verwendet. Die erste Instanz bestimmt, ob das Muster irgendwo in der Zelle auftritt, und die zweite Instanz wird verwendet, um zu bestimmen, ob die extrahierten Zeichen genau mit dem gewünschten Muster übereinstimmen.

_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 (12332) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.

Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Extrahieren eines Musters aus Text.