So extrahieren Sie eine Zahl aus einer Zeichenfolge in Excel

Wie man

VonAdam Davidson

Veröffentlicht7. Juni 2023

Excel ist eine leistungsstarke Tabellenkalkulation, kann aber keine Wunder bewirken. Wenn Sie Daten importieren und diese Daten ein Chaos sind, kann Excel nicht viel damit anfangen. Wenn Sie beispielsweise Werte haben, die als Teil einer Zeichenfolge an andere Zeichen angehängt sind, können Sie keine Berechnungen mit ihnen durchführen, es sei denn, Sie extrahieren sie zuerst.

Zum Glück ist genau das möglich, auch wenn es komplizierter sein kann, als Sie zunächst denken. So extrahieren Sie eine Zahl aus einer Zeichenfolge in Excel.

So extrahieren Sie mithilfe von Flash Fill eine Zahl aus dem Ende einer Zeichenfolge

Eine schnelle und einfache Möglichkeit, eine Zahl am Ende einer Zeichenfolge zu extrahieren, ist die Verwendung von Excel Flash-Füllung Besonderheit. Mit dieser Funktion können Sie schnell einen Bereich von Zellen füllen, basierend auf dem in einer bestimmten Zelle erkannten Muster. Ein Nachteil dieser Methode besteht darin, dass Sie die erste Extraktion selbst durchführen müssen. Wenn Sie nur eine Zahl aus einer einzelnen Zeichenfolge extrahieren möchten, ist sie nutzlos.

In den meisten Fällen kann Flash Fill die Zahl am Ende einer Zeichenfolge korrekt extrahieren, obwohl es nicht unfehlbar ist, sollten Sie die Ergebnisse daher immer sorgfältig überprüfen. Wenn es nicht richtig funktioniert, füllen Sie die ersten zwei oder drei Zellen manuell aus und nicht nur die erste.

So extrahieren Sie mit Flash Fill eine Zahl vom Ende einer Zeichenfolge:

1. Wählen Sie die Zelle neben der ersten Zelle aus, die Ihre Zeichenfolgen enthält.
   
2. Geben Sie die Zahl ein, die am Ende der Zeichenfolge erscheint.
   
3. Wenn sich alle Ihre Daten in einer fortlaufenden Spalte ohne Lücken befinden, wählen Sie die Zelle direkt unter der Zelle aus, in die Sie gerade die Zahl eingegeben haben.
   
4. Wenn Ihre Daten in einigen Zeilen Lücken aufweisen, wählen Sie die erste Zelle und alle Zellen darunter aus, aus denen Sie Zahlen extrahieren möchten, einschließlich aller leeren Zeilen.
   
5. Drücken Sie Strg+E unter Windows bzw Befehl+E auf dem Mac, um die Flash Fill-Funktion auszuführen.
6. Ihre verbleibenden Zellen werden automatisch basierend auf dem von Excel erkannten Muster gefüllt. In den meisten Fällen werden die Zahlen am Ende der Zeichenfolge korrekt extrahiert.
   

So extrahieren Sie mithilfe einer Formel eine Zahl aus dem Ende einer Zeichenfolge

Ein Nachteil der Verwendung der Flash Fill-Methode besteht darin, dass Sie selbst Zahlen aus der Zeichenfolge für die erste Zeile extrahieren müssen. Es ist möglich, mithilfe einer Formel eine Zahl aus dem Ende einer Zeichenfolge zu extrahieren, obwohl die Formel recht komplex ist.

So extrahieren Sie mithilfe einer Formel eine Zahl vom Ende einer Zeichenfolge:

1. Klicken Sie in die Zelle, in der die extrahierte Zahl erscheinen soll.
2. Geben Sie die folgende Formel ein und ersetzen Sie „B2“ durch die Zelle, die Ihre Zeichenfolge enthält:

   =VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT(" 1:"&LEN(B2))),0))))

3. Drücken Sie Eingeben.
4. Die Zahl sollte aus der Zeichenfolge extrahiert werden.
5. Um die Formel auf alle verbleibenden Zellen anzuwenden, klicken Sie auf den Ziehpunkt in der unteren rechten Ecke der Zelle.
   
6. Ziehen Sie nach unten über die Zellen, in denen die Formel repliziert werden soll.
   
7. Lassen Sie die Maustaste los und die Formel wird in die ausgewählten Zellen kopiert.
8. Diese Zellen sollten nun Ihre extrahierten Zahlen enthalten.
   
9. Alle Zellen ohne eine Zeichenfolge zum Extrahieren geben einen Fehler zurück. Wenn Sie stattdessen eine leere Zelle zurückgeben möchten, können Sie stattdessen die Formel verwenden:

   =IFERROR(VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. Alle leeren Zellen geben jetzt ein Leerzeichen zurück.
    

Sie fragen sich vielleicht, was diese Formel bewirkt. Kurz gesagt, es wird ein Array generiert, das die gleiche Länge wie der String hat. Es platziert jedes Zeichen aus der Zeichenfolge der Reihe nach in diesem Array. Anschließend wertet es jedes dieser Zeichen aus, um festzustellen, ob es sich um eine Zahl handelt oder nicht. Alle Zeichen, die Zahlen sind, werden in Null umgewandelt. Alle Zeichen, die keine Zahlen sind, werden in eine Zahl umgewandelt, die ihre Position in der Zeichenfolge darstellt.

AB5HG23 würde beispielsweise in 1, 2, 0, 4, 5, 0, 0 umgewandelt werden. Anschließend wird in dieser Liste nach dem höchsten Wert gesucht, also der Position des letzten Zeichens, das keine Zahl ist. Schließlich werden alle Zeichen nach diesem Punkt zurückgegeben, bei denen es sich um die Zahlen am Ende der Zeichenfolge handelt. Die VALUE-Funktion wandelt dies dann wieder in eine Zahl um. Ganz einfach, oder?

So extrahieren Sie mithilfe von Flash Fill eine Zahl vom Anfang einer Zeichenfolge

Sie können Flash Fill auch verwenden, um eine Zahl am Anfang einer Zeichenfolge zu extrahieren, indem Sie das erste Beispiel selbst ausfüllen und Excel erlauben, das Muster zu stoppen.

So extrahieren Sie mit Flash Fill eine Zahl vom Anfang einer Zeichenfolge:

1. Wählen Sie die Zelle rechts neben der ersten Zeichenfolge aus, die eine Zahl enthält, die Sie extrahieren möchten.
2. Geben Sie die Zahl ein, die am Anfang der Zeichenfolge erscheint.
   
3. Wenn sich alle Ihre Daten in einer fortlaufenden Spalte ohne Lücken befinden, wählen Sie die Zelle direkt darunter aus.
   
4. Wenn Ihre Daten Lücken aufweisen, wählen Sie die erste Zelle und alle Zellen darunter aus, aus denen Sie Zahlen extrahieren möchten, einschließlich aller leeren Zeilen.
   
5. Drücken Sie Strg+E unter Windows bzw Befehl+E auf dem Mac, um Flash Fill zu starten.
6. Die von Ihnen ausgewählten Zellen sollten nun die extrahierten Zahlen anzeigen.
   

So extrahieren Sie mithilfe einer Formel eine Zahl vom Anfang einer Zeichenfolge

Sie können auch eine Formel verwenden, um eine Zahl vom Anfang einer Zeichenfolge zu extrahieren. Dies funktioniert ähnlich wie die Formel im vorherigen Abschnitt, ermittelt jedoch die Position des ersten nicht numerischen Zeichens und gibt alle Zeichen davor zurück.

So extrahieren Sie mithilfe einer Formel eine Zahl vom Anfang einer Zeichenfolge:

1. Wählen Sie die Zelle aus, in der die extrahierte Zahl angezeigt werden soll.
2. Geben Sie die folgende Formel ein und ersetzen Sie „B18“ durch die Zelle, die Ihre Zeichenfolge enthält:

   =VALUE(LEFT(B18,MATCH(FALSE, ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Drücken Sie Eingeben und die Nummer wird extrahiert.
   
4. Du kannst Verwenden Sie Autofill in Excel, um die Formel in alle anderen gewünschten Zellen zu kopieren.
5. Wenn Sie möchten, dass leere Zeilen ein leeres Ergebnis zurückgeben, verwenden Sie stattdessen die folgende Formel:

   =IFERROR(VALUE(LEFT(B18,MATCH(FALSE, ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))," ")

So extrahieren Sie Zahlen an einer beliebigen Stelle in einer Zeichenfolge mithilfe von Flash Fill

Wie bereits erwähnt, funktioniert Flash Fill möglicherweise nicht, wenn die erste Zelle nicht mit dem Format einiger anderer Zellen übereinstimmt. In diesem Fall müssen Sie möglicherweise zwei oder drei Zellen manuell berechnen, bevor Flash Fill Ihnen das gewünschte Ergebnis liefert.

So extrahieren Sie mit Flash Fill Zahlen an einer beliebigen Stelle in einer Zeichenfolge:

1. Geben Sie in der Zelle neben der ersten Zeichenfolge manuell die in der Zeichenfolge enthaltenen Zahlen ein.
   
2. Wenn sich alle Ihre Daten in einer fortlaufenden Spalte befinden, wählen Sie die Zelle direkt darunter aus.
   
3. Wenn Ihre Daten Lücken aufweisen, wählen Sie die erste Zelle und alle Zellen darunter aus, aus denen Sie Zahlen extrahieren möchten, einschließlich aller leeren Zeilen.
   
4. Drücken Sie Strg+E unter Windows bzw Befehl+E auf dem Mac, um Flash Fill zu starten.
5. Die Zellen sollten sich nun mit den extrahierten Zahlen füllen.
   

So extrahieren Sie Zahlen an einer beliebigen Stelle in einer Zeichenfolge mithilfe einer Formel

Mit einer Formel können Sie alle Zahlen aus einer Zeichenfolge extrahieren, unabhängig davon, wo sie vorkommen.

So extrahieren Sie Zahlen an einer beliebigen Stelle in einer Zeichenfolge mithilfe einer Formel:

1. Wählen Sie die Zelle aus, in der die extrahierte Zahl angezeigt werden soll.
2. Geben Sie die folgende Formel ein und ersetzen Sie „B9“ durch die Zelle, die Ihre Zeichenfolge enthält:

   =SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9) )),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. Ihre Formel sollte alle Zahlen aus der Zeichenfolge extrahieren.
   
4. Wenn Sie möchten, dass leere Zellen eine leere Zelle anstelle von Null zurückgeben, verwenden Sie stattdessen diese Formel:

   =IFERROR(SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN( B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)," ")

5. Du kannst Verwenden Sie Autofill, um die Formel auf alle anderen Zellen in Ihrer Tabelle anzuwenden.
6. Alle Zeichenfolgen, die keine Zahlen enthalten, geben eine Null zurück. Sie können lernen, wie es geht Wandeln Sie in Excel eine Null in einen Bindestrich um wenn Sie dies ändern möchten.

Erweitern Sie Ihre Excel-Kenntnisse

Wenn Sie lernen, wie Sie in Excel eine Zahl aus einer Zeichenfolge extrahieren, können Sie schnell die benötigten Daten aus einer Liste von Zeichenfolgen extrahieren. Auch wenn die erforderlichen Formeln komplex sein können, müssen Sie nicht genau verstehen, wie sie funktionieren; genau das tun sie!

Es gibt weitere nützliche Excel-Tricks, die Sie lernen können, um Ihre Fähigkeiten auf die nächste Stufe zu bringen. Du kannst lernen wie man Zellen mit Text zählt in ihnen, oder wie man Berechnen Sie das Alter einer Person anhand ihres Geburtsdatums. Du kannst sogar Erstellen Sie einen Zufallszahlengenerator in Excel.