Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Mit VBA Zahlen aus Text extrahieren

Mit VBA Zahlen aus Text extrahieren
20.07.2023 09:05:27
Erwin
Hallo Excel-Gemeinde,

ist es möglich mittels VBA einen Bereich, indem die Zellwerte mit Text oder Text/Zahlen völlig unterschiedlich befüllt sind, gezielt nach Zahlen zu suchen, diese dann zu
extrahieren und in einer anderen Spalte wieder einzufügen.
Also konkret habe ich in der Spalte T etwa 80.000 befüllte Zellen mit nur Text oder Text/Zahlen (keine feste Struktur, die Zahlen stehen durcheinander). Eine Schleife soll den Bereich
in Spalte T durchlaufen und wenn sich in einer Zelle Text mit Zahlen befindet diese Zahl extrahieren und in der gleichen Zeile in Spalte AJ wieder einfügen.
Das blöde an der Sache ist, dass aber nur bestimmte Zahlen ausgelesen werden sollen, z.B. 60,70,80,120,140,150,160,170,180,190,200 und noch weitere 40ig verschiedene.

Ich habe es mit der Formel {=VERWEIS(9^9;--TEIL(T5;MIN(WENN(ISTZAHL(--TEIL(T5;SPALTE(1:1);1));SPALTE(1:1)));SPALTE(1:1)))} schon versucht, hat auch geklappt, aber erstens hat das Kopieren
nach unten eine Ewigkeit gedauert und zweitens hat es mir ALLE Zahlen ausgelesen und in Spalte AJ geschrieben. Ich brauch aber nur bestimmte Zahlen wie die oben Genannten. Sonst muss
ich ja nach dem Auslesen wieder mit Filtern beginnen.

Wenn dies mit VBA möglich wäre, vor allem nach dem Kriterium nur bestimmte Zahlen zu extrahieren, glaube ich wäre es insgesamt schneller und vor allem ohne umständliches Nacharbeiten.

Wenn mir jemand helfen könnte wäre dies super!
Schöne Grüße
Erwin
Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mit VBA Zahlen aus Text extrahieren
20.07.2023 09:11:18
Fennek
Hallo,

ohne Kenntnis der Details:

Mit VBA und RegEx sollte es möglich sein. Du müsstest aber eine Beispieldatei mit allen Varianten (in möglichst wenigen Zeilen) bereitstellen.

RegEx ist nicht ganz trivial, Du müsstest dich soweit einarbeiten, dass Du den Code einigermaßen nachvollziehen kannst.

mfg

Anzeige
AW: Mit VBA Zahlen aus Text extrahieren
20.07.2023 09:21:24
Luschi
Hallo Erwin,

für diese Aufgabe ist doch Power Querry bestens geeignet:
- keine Formeln & kein Vba
- PQ besitzt die eingebaute Intelligenz, Zellenwerte beim Wechsel von
  Buchstabe zu Zahl und auch umgekehrt zu teilen

Gruß von Luschi
aus klein-Paris

Anzeige
AW: Mit VBA Zahlen aus Text extrahieren
20.07.2023 09:33:23
Erwin
Hallo Fennek und Luschi,
vielen Dank für eure schnelle Antworten.
Weiter tiefer will ich aber nicht einsteigen, dazu fehlt mir dann doch die nötige Kompetenz.
Luschi, aber das mit dem PQ würde ich schon gerne mal ausprobieren, aber ich weiß nicht mal wo ich das finde und wie ich es bediene.
Schöne Grüße
Erwin

Anzeige
AW: Mit VBA Zahlen aus Text extrahieren
21.07.2023 13:09:41
Luschi
Hallo PQ-Fan's

hier mal eine erweiterte PQ-Lösung:
- mehrere Zahlen pro Excel-Zelle möglich
- Mehrfach-Schritte in 2 Funktionen ausgelagert
- dynamische Spaltensplittung, damit zum Schluß jede Zahl in eine Zelle steht

https://www.herber.de/bbs/user/160011.xlsx

Gruß von Luschi
aus klein-Paris

Anzeige
AW: Mit VBA Zahlen aus Text extrahieren
20.07.2023 10:21:37
Daniel
HI
kannst du mal eine Beispieldatei hochladen, die Datei sollte ein paar typische Fälle enthalten so wie dein Wunschergebnis für diese Daten.
Gruß Daniel

AW: Mit VBA Zahlen aus Text extrahieren
20.07.2023 10:36:55
Erwin
Hallo Daniel,
hochladen ist leider nicht möglich. Ich habs jetzt mit Power Query versucht und die Zahlen wenigstens herausgefiltert und kann so in kleinen Schritten weiterarbeiten.
Danke Dir und euch allen
Schöne Grüße
Erwin

Anzeige
AW: Mit VBA Zahlen aus Text extrahieren
20.07.2023 11:09:51
Daniel
warum ist hochladen nicht möglich?
es müssen ja nur ein paar Werte drin stehen, die vielleicht noch nicht mal echt sind.
das solltest du von Hand erstellen können, so dass es unproblematisch ist.
wie sind den die die Werte in der Zelle von einander getrennt?

aber vielleicht mal ein Lösungsansatz (den ich leider wegen altem Excel nicht testen kann)

1. die gesuchten Werte in einer Spalte auflisten (jeden Wert in einer Zelle), z.B in A1:A40
2. in Spalte AJ dann eine Formel in dieser Art: =Textverketten("; ";Wahr;Wenn(IstZahl(Finden($A$1:$A$40;T5));$A$1:$A$40;""))
(ggf Matrixformelabschluss mit STRG+SHIFT+ENTER erforderlich)

wenn die Zahlen unterschiedlich lang sind, müsste man für die Suche noch das Trennzeichen mit dazu nehmen, sonst bekommst du bei "60" einen Treffer, obwohl 160 in der Zelle steht, dh wenn die einzelnen Werte mit Leerzeichen getrennt sind, muss man mit nach " 60 " suchen, damit man auch nur bei 60 einen Treffer bekommt und nicht auch bei 160:
=Textverketten("; ";Wahr;Wenn(IstZahl(Finden(" "&$A$1:$A$40&" ";" "&T5&" "));$A$1:$A$40;""))
Gruß Daniel

Anzeige
AW: Mit VBA Zahlen aus Text extrahieren
20.07.2023 13:26:04
Piet
Hallo

mir ist eine Idee gekommen, die ich noch nie ausprobiert habe. Würde mich interessieren ob das bei 80.000 Zeilen klappt??

mfg Piet

  • Sub Text_löschen()
       Application.ScreenUpdating = False
       'Replace alle Zeichen bis zur Zahl 0
       For i = 1 To 47
          Columns("xx").Replace Chr(i), "", xlPart
       Next i
       'Replace alle Zeichen nach der Zahl 9
       For i = 58 To 255
          Columns("xx").Replace Chr(i), "", xlPart
       Next i
    End Sub


  • Anzeige
    AW: Mit VBA Zahlen aus Text extrahieren
    20.07.2023 13:56:50
    Daniel
    muss man ausprobieren.
    im Normalfall sollte es ausreichen, sich auf die Buchstaben des Alphabes inkl Umlaute sowie die gängigen Satzzeichen zu beschränken.
    Groß- und Kleinbuchstaben kann man gemeinsam eleminieren.

    aber wir müssen erstmal ein konkretes Beispiel haben, wie die Daten vorliegen und wie das Ergebnis aussehen soll, also gibt es immer nur eine einzige Zahl pro Zelle usw.

    Gruß Daniel

    Anzeige
    ;

    Forumthreads zu verwandten Themen

    Anzeige
    Anzeige
    Anzeige
    Entdecke relevante Threads

    Schau dir verwandte Threads basierend auf dem aktuellen Thema an

    Alle relevanten Threads mit Inhaltsvorschau entdecken
    Anzeige
    Anzeige

    Infobox / Tutorial

    Mit VBA Zahlen aus Text extrahieren


    Schritt-für-Schritt-Anleitung

    Um Zahlen aus Zellen zu extrahieren, die Text mit Zahlen enthalten, kannst Du VBA verwenden. Hier ist eine einfache Schritt-für-Schritt-Anleitung:

    1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.

    2. Gehe zu "Einfügen" > "Modul", um ein neues Modul zu erstellen.

    3. Füge den folgenden VBA-Code ein:

      Sub ExtrahiereZahlen()
         Dim Zelle As Range
         Dim Zahlen As String
         Dim i As Integer
         Dim Bereich As Range
      
         ' Definiere den Bereich, in dem die Daten stehen
         Set Bereich = Range("T1:T80000")
      
         For Each Zelle In Bereich
             Zahlen = ""
             For i = 1 To Len(Zelle.Value)
                 If IsNumeric(Mid(Zelle.Value, i, 1)) Then
                     Zahlen = Zahlen & Mid(Zelle.Value, i, 1)
                 End If
             Next i
             ' Hier kannst Du Deine speziellen Zahlen überprüfen
             If InStr("607080120140150160170180190200", Zahlen) > 0 Then
                 Zelle.Offset(0, 16).Value = Zahlen ' In Spalte AJ einfügen
             End If
         Next Zelle
      End Sub
    4. Schließe den VBA-Editor und gehe zurück zu Excel.

    5. Drücke ALT + F8, wähle ExtrahiereZahlen und klicke auf Ausführen.

    Dieser Code durchsucht die Zellen in Spalte T nach Zahlen und fügt die gefundenen Zahlen in der gleichen Zeile in Spalte AJ ein, wenn sie den Kriterien entsprechen.


    Häufige Fehler und Lösungen

    • Fehler: "Typ nicht definiert"

      • Stelle sicher, dass Du alle notwendigen Variablen korrekt deklariert hast.
    • Fehler: Zahlen werden nicht gefunden

      • Überprüfe den Suchstring in der If-Bedingung. Stelle sicher, dass die Zahlen, die Du suchst, korrekt eingegeben sind.
    • Die Schleife läuft sehr langsam

      • Reduziere den Bereich, der durchsucht wird, oder verwende effizientere Suchmethoden wie RegEx.

    Alternative Methoden

    Wenn Du kein VBA verwenden möchtest, kannst Du auch Power Query nutzen, um Zahlen aus Textdaten zu extrahieren. Power Query kann automatisch erkennen, wenn sich in einer Zelle Zahlen und Text befinden und diese entsprechend trennen.

    1. Gehe zu "Daten" > "Daten abrufen" > "Aus Tabelle/Bereich".
    2. Wähle den Bereich aus, den Du analysieren möchtest.
    3. Klicke auf "Transformieren", um den Power Query Editor zu öffnen.
    4. Verwende die Funktionen im Editor, um die Spalten zu teilen und die gewünschten Zahlen zu extrahieren.

    Praktische Beispiele

    Hier sind einige Beispiele, wie der obige VBA-Code angewendet wird:

    • Beispiel 1:

      • Zelle T1 enthält den Text "Die Temperatur ist 80 Grad."
      • Nach Ausführung des Codes wird in Zelle AJ1 "80" eingefügt.
    • Beispiel 2:

      • Zelle T2 enthält "60, 70 und 90 Punkte".
      • Nach Ausführung des Codes wird "60" in AJ2 eingefügt.

    Diese Beispiele verdeutlichen, wie Du mit dem VBA-Code gezielt Zahlen aus Text extrahieren kannst.


    Tipps für Profis

    • RegEx verwenden: Mit regulären Ausdrücken kannst Du spezifische Muster identifizieren und extrahieren, was die Flexibilität erhöht.
    • Optimierung: Teste Deinen Code mit kleineren Datenmengen, bevor Du ihn auf 80.000 Zeilen anwendest, um sicherzustellen, dass er effizient läuft.
    • Debugging: Nutze Debug.Print im Code, um Informationen während der Ausführung zu protokollieren.

    FAQ: Häufige Fragen

    1. Kann ich nur bestimmte Zahlen extrahieren?
    Ja, im VBA-Code kannst Du die Zahlen in der If-Bedingung anpassen, um nur bestimmte Werte zu extrahieren.

    2. Was ist der Unterschied zwischen VBA und Power Query für diese Aufgabe?
    VBA bietet mehr Flexibilität bei der Programmierung, während Power Query eine benutzerfreundliche Oberfläche bietet, um Daten zu transformieren und zu filtern, ohne Programmierkenntnisse.

    3. Wie kann ich den Code anpassen, um mehr als eine Zahl pro Zelle zu extrahieren?
    Du könntest den Code so anpassen, dass er eine Liste von gefundenen Zahlen in die Zelle schreibt, anstatt nur die erste gefundene Zahl.

    4. Ist dieser Prozess auch in älteren Excel-Versionen möglich?
    Ja, der VBA-Code sollte in den meisten Excel-Versionen funktionieren, jedoch können einige Funktionen in älteren Versionen von Excel eingeschränkt sein.

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Entdecke mehr
    Finde genau, was du suchst

    Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

    Suche nach den besten Antworten
    Unsere beliebtesten Threads

    Entdecke unsere meistgeklickten Beiträge in der Google Suche

    Top 100 Threads jetzt ansehen
    Anzeige