Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

Forumthread: Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

20.09.2016 10:34:04

Manuel

Hallo zusammen,
ich stehe vor einem Problem bei dem ich irgendwie eine Denkblokade habe.
In Spalte B habe ich Texte mit Zahlen. Es können mal mehrere sein, mal nur eine, mal gar keine, unterschiedliche Längen und an völlig unterschiedlichen Positionen.
Beispiele:
Text Text - -
TEXT: TEXT - TEXT - 53421, 98765, 12345, - Text [TTT123] - 123456, 654321
Ich möchte mir die genau 5-stelligen Zahlen ausgeben lassen.
Ich habe es mit dieser Formel versucht:
=VERWEIS(9^9;1*TEIL(B1&"#";SPALTE(1:1);6))
Das klappt bei Zahlen im Format xx.xxxx und wenn eine 5-stellige Zahl (xxxxx) enthalten ist.
Ich bekomme aber nur EINE Zahl extrahiert und sobald 6-stellige Zahlen enthalten sind werden diese auch (gekürzt) mit ausgeben.
Bekomme ich das überhaupt über eine Formel gelöst oder wird es eine VBA Lösung?
Hat jemand eine Idee oder Tipp? Ich glaube ich sitze einfach schon zu lange davor.
Schöne Grüße
Manuel

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: wieder ein Fall für AGGREGAT() ...

20.09.2016 12:24:59

...

Hallo Manuel,
... wobei es am einfachsten ist, die Zahlen der Größe nach sortiert auszugeben.
Wenn die Reihenfolge der 5-Steller innerhalb des Textes bei deren Auflisting berücksichtigt werden soll, dann kommt zusätzlich noch INDEX() ins Spiel.
Evtl. müsste die WECHSEL()ei noch erweitert werden, wenn z.B. noch Doppelpunkte, Klammern oder bestimmte Buchstaben als Trennzeichen berücksichtigt werden müssen.
Folgende Formel:
=WENNFEHLER(AGGREGAT(15;6;--TEIL("!"&WECHSELN(WECHSELN($B1;" ";"!");",";"!")&"!";SPALTE(1:1);5)
/(LINKS(TEIL("!!"&WECHSELN(WECHSELN($B1;" ";"!");",";"!")&"!";SPALTE(1:1);5);1)="!")
/(LINKS(TEIL(WECHSELN(WECHSELN(RECHTS($B1;LÄNGE($B1)-4);" ";"!");",";"!")&"!";SPALTE(1:1);5);1)="!");SPALTE(A1));"")
und diese nach rechts kopieren.
Gruß Werner
.. , - ...

AW: wieder ein Fall für AGGREGAT() ...

20.09.2016 12:59:11

Manuel

Hallo Werner, vielen Dank!
AGGREGAT, werde ich mir direkt mal anschauen. Damit habe ich tatsächlich noch nichts gemacht.
Die Reihenfolge der Zahlen ist mir total egal. Eine Ausgabe als kommaseparierte Liste hätte mir auch vollkommen gereicht.
Allerdings gibt die Formel keine Zahlen aus, die nicht "freistehen". Also z. B. etwas wie
+12345
.12345
12345>
Ich schaue mal ob ich das hinbekommen.
Besten Dank noch mal,
Manuel

AW: dann erweiterte WECJHSEL()ei und ISTFEHL() ..:

20.09.2016 14:07:04

...

Hallo Manuel,
... wie ich schon schrieb, allgemeiner z.B. so:
=WENNFEHLER(AGGREGAT(15;6;--TEIL("!"&WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN
($B1;"";"!");",";"!");"+";"!");"-";"!");":";"!");".";"!")&"!";SPALTE(1:1);5)
/ISTFEHL(--LINKS(TEIL("!!"&$B1;SPALTE(1:1);1);1))
/ISTFEHL(--LINKS(TEIL(RECHTS($B1;LÄNGE($B1)-4)&"!";SPALTE(1:1);5);1));SPALTE(A1));"")
Gruß Werner
.. , - ...

Infobox / Tutorial

Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um mehrere Zahlen aus einer Zelle auszulesen, die sowohl Text als auch Zahlen enthält, kannst Du die folgenden Schritte befolgen. Diese Anleitung nutzt die AGGREGAT-Funktion in Excel.

Öffne Dein Excel-Dokument und navigiere zu der Zelle, in der Du die Formel einfügen möchtest.

Gib die folgende Formel ein:

=WENNFEHLER(AGGREGAT(15;6;--TEIL("!"&WECHSELN(WECHSELN($B1;" ";"!");",";"!")&"!";
SPALTE(1:1);5)/(LINKS(TEIL("!!"&WECHSELN(WECHSELN($B1;" ";"!");",";"!")&"!";
SPALTE(1:1);5);1)="!")/(LINKS(TEIL(RECHTS($B1;LÄNGE($B1)-4);"!"&SPALTE(1:1);5);1)="!");
SPALTE(A1));"")

Passe die Zellreferenz ($B1) an die Zelle an, aus der Du die Zahlen auslesen möchtest.
Kopiere die Formel nach rechts, um weitere Zahlen zu extrahieren.

Mit dieser Formel kannst Du effizient Zahlen aus einer Zelle auslesen, selbst wenn sie von Text umgeben sind.

Häufige Fehler und Lösungen

Fehler: Keine Zahlen gefunden
- Lösung: Überprüfe, ob die Zelle tatsächlich die gewünschten Zahlen enthält und ändere die Zellreferenz in der Formel.
Fehler: Falsche Zahlenlängen werden ausgegeben
- Lösung: Stelle sicher, dass die TEIL-Funktion korrekt konfiguriert ist, um nur die gewünschten Längen zu extrahieren.
Fehler: Formel gibt Fehlerwerte aus
- Lösung: Verwende WENNFEHLER, um sicherzustellen, dass bei Fehlern eine leere Zelle oder ein definierter Wert zurückgegeben wird.

Alternative Methoden

Wenn Du eine andere Herangehensweise ausprobieren möchtest, kannst Du auch VBA verwenden, um die Zahlen aus einer Zelle zu extrahieren. Hier ein einfaches Beispiel für ein VBA-Makro:

Öffne den VBA-Editor (Alt + F11).

Füge einen neuen Modul hinzu und kopiere den folgenden Code:

Function ExtractNumbers(cell As Range) As String
   Dim matches As Object
   Dim regex As Object
   Set regex = CreateObject("VBScript.RegExp")
   regex.Global = True
   regex.Pattern = "\d{5}" ' Nur 5-stellige Zahlen

   If regex.Test(cell.Value) Then
       Set matches = regex.Execute(cell.Value)
       Dim result As String
       Dim i As Integer
       For i = 0 To matches.Count - 1
           result = result & matches(i) & ", "
       Next i
       ExtractNumbers = Left(result, Len(result) - 2) ' Entferne das letzte Komma
   Else
       ExtractNumbers = ""
   End If
End Function

Verwende die Funktion in einer Zelle wie folgt: =ExtractNumbers(B1).

Praktische Beispiele

Beispiel 1: Wenn in Zelle B1 der Text TEXT: TEXT - 12345, 67890 - Text steht und Du die Formel anwendest, wird 12345 und 67890 extrahiert.
Beispiel 2: Bei TEXT - 53421, +12345, .67890 gibt die VBA-Funktion 53421, 12345, 67890 zurück.

Tipps für Profis

Verwende die Funktion TEXTVERKETTEN (Excel 365), um die Zahlen zu einer kommaseparierten Liste zusammenzuführen.
Nutze FILTER(), um die Ergebnisse dynamisch zu filtern, falls Du mit Excel 365 arbeitest.
Kombiniere diese Techniken: Du kannst AGGREGAT mit VBA für eine noch leistungsfähigere Lösung kombinieren.

FAQ: Häufige Fragen

1. Wie kann ich nur Text aus einer Zelle auslesen? Verwende die Funktion =TEXTWERT(B1) um nur den Text aus der Zelle zu extrahieren.

2. Was ist der Unterschied zwischen AGGREGAT und VERWEIS? AGGREGAT bietet mehr Flexibilität bei der Handhabung von Fehlern und kann auch mit Arrays arbeiten, während VERWEIS einfachere Suchvorgänge ermöglicht.

3. Funktioniert das in allen Excel-Versionen? Die gezeigten Formeln funktionieren in Excel 2010 und neueren Versionen. VBA ist in allen Versionen verfügbar.

Beliebteste Forumthreads (12 Monate)

Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

Forumthread: Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

Forumthread: Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Auslesen mehrerer Zahlen aus Zelle mit Text & Zahl

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread