Wie man in Excel eine Zahl aus einer Zeichenfolge extrahiert: Die richtige Herangehensweise finden

Wenn du mit Excel arbeitest, um Daten zu bereinigen, wirst du vielleicht auf die Aufgabe stoßen, Zahlen aus alphanumerischen Zeichenfolgen zu extrahieren. Dieses Problem tritt häufig auf, wenn du Daten hast, bei denen Zahlen in Zeichenketten eingebettet sind, z. B. Produktcodes oder Umfrageantworten.

In diesem Artikel zeige ich dir die verschiedenen Methoden, mit denen du Zahlen aus Zeichenketten extrahieren kannst, indem du Formeln wie RIGHT(), LEN() und FIND() für verschiedene Anwendungsfälle verwendest. Wenn du mit einigen der Grundlagen nicht vertraut bist, empfehle ich dir unseren Kurs Einführung in Excel, um eine solide Grundlage in Excel zu schaffen. Du solltest dir auch unsere Datenaufbereitung in Excel ansehen, um mehr über die Umwandlung von Rohdaten zu erfahren.

Excel-Formeln zum Extrahieren von Zahlen aus einer Zeichenfolge

Excel bietet verschiedene formelbasierte Methoden, um Zahlen aus Zeichenketten zu extrahieren. Jede Methode eignet sich für unterschiedliche Bedürfnisse, je nachdem, wo sich die Zahl in der Zeichenfolge befindet. Wenn du unter Zeitdruck stehst, kannst du gerne zu dem Abschnitt springen, der dein dringendstes Bedürfnis befriedigt.

Zahlen vom Anfang einer Zeichenfolge in Excel extrahieren

Manchmal kann es vorkommen, dass die Zahlen am Anfang der Zeichenkette stehen. In solchen Fällen verwendest du LEFT() und FIND(), um die Zahlen aus der Zeichenkette zu extrahieren. Wenn du die Funktionen in einer Formel kombinierst, werden die Zahlen nach der folgenden Logik extrahiert:

• FIND(): Findet die Position des ersten Buchstabens in der Zeichenkette.

• LEFT(): Extrahiert die Zeichen vom Anfang der Zeichenkette bis zur Position des ersten Buchstabens minus eins.

Der erste Teil der folgenden Formel findet den ersten Buchstaben in der Zeichenkette. Der zweite Teil extrahiert alles vor diesem Buchstaben und lässt nur die führenden Zahlen übrig. Wenn keine Buchstaben vorhanden sind, wird die gesamte Zeichenkette zurückgegeben.

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Extrahieren von Zahlen aus dem Anfang einer Zeichenkette in Excel. Bild vom Autor.

Diese Methode ist geeignet, wenn du mit Datensätzen arbeitest, die üblicherweise numerische Präfixe verwenden, wie z. B. Bestellnummern, Mitglieder-IDs und Chargennummern.

Extrahieren von Zahlen aus dem Ende einer Zeichenkette in Excel

Wenn du in Excel Zahlen aus dem Ende einer Zeichenfolge extrahieren möchtest, musst du die Funktionen RIGHT(), LEN() und FIND() verwenden. Angenommen, du hast die folgenden Daten in der Excel-Tabelle. Du kombinierst die Funktionen, um die Zahl zu extrahieren, wo:

• FIND(): Findet die Position der ersten Ziffer in der Zeichenkette.

• LEN(): Berechnet die Anzahl der Zeichen von der ersten Ziffer bis zum Ende der Zeichenkette.

• RIGHT(): Extrahiert die Zeichen von der ersten Ziffer bis zum Ende der Zeichenkette.

Die folgende Formel entfernt den gesamten vorangestellten Text und gibt die erste Zahl zusammen mit allem, was in der Zeichenkette folgt, zurück.

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)

Extrahieren von Zahlen aus dem Ende einer Zeichenkette in Excel. Bild vom Autor.

Diese Methode ist vor allem bei Datensätzen mit konsistenten alphanumerischen Zeichen nützlich, z. B. bei Produkt-SKUs, Bestell-IDs und Seriennummern.

Zahlen aus einer beliebigen Position in einer Zeichenfolge in Excel extrahieren

Wenn du einen Datensatz hast, bei dem die Zahlen irgendwo in der Zeichenfolge vorkommen, brauchst du erweiterte Formeln mit TEXTJOIN(), MID() und ROW(), um die Zahlen zu extrahieren.

Wenn du diese Funktionen in einer Formel kombinierst, kannst du mit der folgenden Logik eine Zahl aus einer Zeichenkette extrahieren: 

• ROW() und MID(): Durchläuft jedes Zeichen der Zeichenkette, um festzustellen, ob es numerisch ist.

• IFERROR(): Logischer Test, um die identifizierten Zahlen von anderen Zeichen zu isolieren.

• TEXTJOIN(): Kombiniere alle extrahierten numerischen Zeichen zu einer einzigen Zahlenzeichenfolge.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Extrahiere eine Zahl aus einer beliebigen Position in einer Zeichenkette. Bild vom Autor.

Diese Methode eignet sich am besten, wenn dein Datensatz alphanumerische Zeichen enthält, die an beliebiger Stelle in der Zeichenkette vorkommen. Hinweis: Diese Funktionen sind nicht alle in älteren Excel-Versionen verfügbar. In unserem Lernpfad Excel-Grundlagen erfährst du mehr über die Funktionen und Formeln, die in den neuesten Excel-Versionen verfügbar sind.

Zahlen extrahieren, indem unerwünschte Zeichen herausgefiltert werden

Manchmal sind deine Daten unordentlich. Zum Glück kannst du die Funktionen SUBSTITUTE() und TEXTJOIN() kombinieren. Folge einfach diesen Schritten:

• SUBSTITUTE(): Die Funktion SUBSTITUTE() kann verwendet werden, um alle nicht numerischen Zeichen in einer Zeichenfolge durch ein Trennzeichen zu ersetzen, z. B. ein Leerzeichen oder eine leere Zeichenfolge. Du kannst zum Beispiel Buchstaben und Sonderzeichen durch Leerzeichen ersetzen.

• TEXTJOIN(): Die Funktion TEXTJOIN() kann dann verwendet werden, um die verbleibenden Zeichen (die jetzt nur noch Zahlen sein sollten) zu einem einzigen String zu verketten.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Extrahieren von Zahlen aus einer Zeichenkette durch Filtern von Zeichen. Bild vom Autor.

Wenn du Zahlen aus einer Zeichenfolge in Excel extrahieren musst

Schauen wir uns nun zwei kleine Fallstudien an, um etwas Übung im Extrahieren von Zahlen aus Zeichenketten zu bekommen.

Beispiel 1: Nummern aus Produktcodes extrahieren

Angenommen, du verwaltest einen Produktbestand, bei dem jeder Artikel durch einen eindeutigen Produktcode identifiziert wird. Diese Produktcodes sind alphanumerische Zeichenfolgen wie "PROD1234", "ITEM5678" oder "SKU91011".

Um also die numerische Produkt-ID aus den alphanumerischen Produktcodes zu extrahieren, verwenden wir Funktionen, die auf der Position der Zahl basieren. Denke daran: Wenn die Zahlen am Ende stehen, wende RIGHT(), LEN() und FIND() an. Wenn sie am Anfang erscheinen, benutze LEFT() und FIND().

Das folgende Beispiel extrahiert den numerischen Teil der Produktcodes auf der rechten Seite des Codes.

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))

Extrahieren von Zahlen aus Produktcodes in Excel. Bild vom Autor.

Beispiel 2: Umfragedaten mit eingebetteten Zahlen

Umfragedaten enthalten manchmal sowohl Text als auch Zahlen wie "5 von 10 Punkten" oder "Punktzahl": 8," oder "3 Sterne". Wir müssen noch ein bisschen arbeiten, bevor wir Durchschnittswerte berechnen oder Trends erkennen können.

Da Zahlen überall im Text vorkommen können, ist eine dynamische Formel mit TEXTJOIN(), MID() und ROW() erforderlich.

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Zahlen von einer beliebigen Position in Excel extrahieren. Bild vom Autor.

Kopieren und Einfügen von Codebeispielen für besondere Fälle

Im letzten Abschnitt möchte ich dir einige zusätzliche Formeln geben, die verschiedene und spezifische Szenarien behandeln. Du kannst es gerne kopieren und einfügen. Ich hoffe, es löst dein Problem. 

Extrahiere den ersten zusammenhängenden Block von Zahlen

Wenn deine Zeichenkette mehrere Zahlenfolgen enthält, du aber nur die erste Gruppe benötigst, versuche diese Lösung mit den Funktionen LET() und SEQUENCE():

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Extrahieren des ersten zusammenhängenden Blocks aus einer Zeichenfolge in Excel. Bild vom Autor.

In Klammern gesetzte Auszugsnummern

Für Fälle, in denen Zahlen in Klammern stehen, verwende:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Extrahiere die in Klammern gesetzten Zahlen in Excel. Bild vom Autor.

Zahlen nach einem bestimmten Zeichen extrahieren

Wenn deine Daten einen Doppelpunkt als Begrenzungszeichen verwenden (z. B. "Score: 8"), erfasse die Zahlen mit:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Extrahiere die Zahl nach einem Sonderzeichen in Excel. Bild vom Autor.

Dadurch wird alles nach dem Doppelpunkt gezogen. Schließe das Ergebnis mit VALUE() ein, wenn du einen numerischen Wert brauchst. Ersetze den Doppelpunkt, wenn du ein anderes Begrenzungszeichen hast.

Extrahiere alle Zahlen und kombiniere sie zu einem einzigen Wert

Um alle nicht-ziffrigen Zeichen zu entfernen und alle numerischen Zeichen in einer Zeichenkette zu kombinieren, verwende dies:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Extrahiere alle Zahlen und kombiniere sie in Excel zu einem einzigen Wert. Bild vom Autor.

Tipp: In älteren Versionen von Excel gibst du dies als Array-Formel ein (Strg+Umschalt+Eingabe).

Zahlen mit Dezimalpunkten extrahieren

Bei numerischen Zeichenfolgen, die eine Dezimalstelle enthalten können (z. B. "Preis: 45,99"), verwende diese Formel, um sowohl die Ziffern als auch das Dezimalkomma beizubehalten:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Extrahiere Zahlen mit Dezimalpunkten in Excel. Bild vom Autor.

Hinweis: Diese Formel geht davon aus, dass nur ein Dezimalpunkt vorhanden ist.

Fazit

Das Extrahieren von Zahlen aus Zeichenketten ist eine wichtige Technik zur Datenbereinigung. Als Datenanalyst ermutige ich dich, zu lernen, wie man die verschiedenen Formeln anwendet, die in diesem Artikel besprochen werden.

Wenn du deine Excel-Kenntnisse erweitern möchtest, empfehle ich dir unsere Kurse Datenanalyse in Excel und Datenvisualisierung in Excel, um die Datenanalyse und -präsentation zu beherrschen. Probiere auch unseren Kurs Finanzmodellierung in Excel aus, der dir viele Türen öffnen kann.