Live-Forum - Die aktuellen Beiträge
Datum
Titel
09.10.2025 10:43:56
Flagstatus eines Meetingtems nicht abfragbar
09.10.2025 10:00:47
Von zwei Zellen mit Sverweis (B7 und H8) die Summe bilden
09.10.2025 09:48:16
Tabellen mit xverweis zusammen fügen
09.10.2025 09:37:38
Ausgabewert drei Spalten nach Rechts kopieren
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
900to904
900to904
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

aus alphanum. String alles ausser Zahl entfernen

Forumthread: aus alphanum. String alles ausser Zahl entfernen

aus alphanum. String alles ausser Zahl entfernen
29.08.2007 17:55:00
Peter
Guten Abend
Ich habe in der Tabelle "Ursprung" in Spalte A von Zeile 1 bis 200 verschiedene numerische Einträge (nur Zahlen) und weitere alphanumerische Einträge.
Nun möchte ich diese Spalte von Zeile 1 bis 200 in die Tabelle "Ziel" (ist in gleicher Arbeitsmappe wie die Tabelle "Ursprung") kopieren, wobei bei den alphanumerischen Einträge die Buchstaben und allfällige andere Zeichen (jedoch nicht die Zahlen) entfernt werden sollen.
Beispiel
aus AA104949B soll 104949 werden
aus A?10X939_ soll 10939 werden
aus 13A17B19 soll 131719 werden
Habe im Moment keine Idee, wie ich das anpacken kann.
Danke für jede Hilfe.
Peter

Anzeige

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: aus alphanum. String alles ausser Zahl entfernen
29.08.2007 21:29:27
Peter
Hallo Christian
Vielen Dank die Formel ist beeindruckend. Allerdings hätte ich gerne eine Lösung mit VBA, da die zu verarbeitende Zeilenanzahl der Spalte A variabel ist und ich dies gerne automatisieren möchte.
Hat jemand eine Idee?
Danke, Peter

Anzeige
AW: aus alphanum. String alles ausser Zahl entfernen
29.08.2007 21:39:00
Sigi
Hi Peter,
darf's eine VBA-Funktion sein?

Function ZiffernAusString(Text As String) As String
Dim Tx As String
Dim i As Long
For i = 1 To Len(Text)
If Asc(Mid(Text, i, 1)) > 47 And Asc(Mid(Text, i, 1))


Gruß
Sigi

Anzeige
AW: aus alphanum. String alles ausser Zahl entfernen
29.08.2007 21:50:40
Peter
Hallo Sigi
Das ging aber schnell! Selbstverständlich darf es eine Funktion sein. Ich habe eine kleine Ergänzung angebracht, die bewirkt, dass führende NULLEN nicht angezeigt werden, da ich tx mit 1 multipliziere. Allerdings bleibt das Problem, dass wenn der String keine Zahl enthält, "#WERT" zurückgegeben wird. In diesem Fall hätte ich gerne, dass als Wert "" (NICHTS) zurückgegeben wird.
Kannst du mir da (oder jemand anders) auch noch weiterhelfen? Vielen Dank!!
Peter

Function ZiffernAusString(Text As String) As String
Dim Tx As String
Dim i As Long
For i = 1 To Len(Text)
If Asc(Mid(Text, i, 1)) > 47 And Asc(Mid(Text, i, 1))


Anzeige
AW: aus alphanum. String alles ausser Zahl entfernen
29.08.2007 22:01:38
Sigi
Hi Peter,
ich würde die Funktion so lassen, aber den Fall, dass die Zelle leer ist, mit Formel abfangen:
=WENN(A1=""; ""; WERT(ZiffernAusString(A1)))
Gruß
Sigi

AW: aus alphanum. String alles ausser Zahl entfernen
29.08.2007 22:26:14
Peter
Hallo Sigi
Danke für die Rückmeldung. Habe mich wohl nicht ganz deutlich ausgedrückt. Grundsätzlich möchte ich eine Zahl zurück erhalten. Deshalb habe ich mal 1 gerechnet. Es kann Fälle geben, bei denen der String aus lauter Buchstaben besteht, die Zelle also nicht leer ist. Diesen Fall wollte ich mit der gewünschten Erweiterung nach abfangen. Vielen Dank für deine Hilfe.
Peter

Anzeige
Nachfrage...
29.08.2007 21:48:45
Original
Hi,
was, wenn in den Strings Kommata vorkommen, wie soll dann verfahren werden, oder kann das
sicher ausgeschlossen werden?
mfg Kurt

AW: Nachfrage...
29.08.2007 22:28:00
Peter
HalloKurt
Die Nachfrage ist sicher berechtigt. Im vorliegenden Fall handelt es sich um eine Ordnungsnummer, die in verschiedenen Fällen mit Buchstaben ergänzt sein kann, die jedoch für meine Zwecke nicht benötigt werden. Es reicht in jedem Fall, wenn nur die Zahlen oder "" (nichts) zurückgegeben wird.
Gruss, Peter

Anzeige
Dann is ja nix mehr offen... (owT)
30.08.2007 08:43:00
Renee

AW: Doch
30.08.2007 09:09:42
Peter
Hallo Renée
Meine Nachfrage von gestern 21:50 ist noch offen:
Ich habe eine kleine Ergänzung angebracht, die bewirkt, dass führende NULLEN nicht angezeigt werden, da ich tx mit 1 multipliziere. Allerdings bleibt das Problem, dass wenn der String keine Zahl enthält, "#WERT" zurückgegeben wird. In diesem Fall hätte ich gerne, dass als Wert "" (NICHTS) zurückgegeben wird.
Gruss, Peter

Function ZiffernAusString(Text As String) As String
Dim Tx As String
Dim i As Long
For i = 1 To Len(Text)
If Asc(Mid(Text, i, 1)) > 47 And Asc(Mid(Text, i, 1))


Anzeige
AW: Doch
30.08.2007 09:26:44
Renee
Hi Peter,
Sorry, hab nicht aufgepasst.

lösche:        Tx = Tx * 1
ersetze:       ZiffernAusString = Format(Tx, "#")
oder:          ZiffernAusString = Format(Tx, "0")


Greetz Renee

AW: Doch
30.08.2007 10:42:00
Peter
Hallo Renée
Vielen Dank. Das stimmt so, allerdings nur "optisch". Wenn die aus dem String verbleibenden Zahlen mit NULL beginnen, sind diese ohne die Multiplikation im String enthalten, werden entweder angezeigt "0" oder nicht "#". Das Problem ist, dass ich anschliessend eine SVERWEIS oder eine SUMMEWENN Abfrage vornehme und in der anderen Tabelle führende Nullen nicht vorhanden sind. Deshalb war ich auf die Idee gekommen zu mulitiplizieren, wo dann allerdings das Problem aufgetaucht ist, dass bei einem String ohne Zahl eine Multiplikation nicht möglich ist. Deshalb wollte ich die Multiplikation lassen (führende Nullen sind so "erledigt") und zusätzlich eine Abfrage einbauen, die verhindert, dass bei einem String ohne Zahl nicht "#WERT" zurück gegeben wird.
Kannst du mir da noch einen Tipp geben?
Danke, Peter

Anzeige
AW: Doch
30.08.2007 11:04:44
Renee
Hallo Peter,
Was heisst für Dich Das stimmt so, allerdings nur "optisch".
Hast Du die Änderung ausprobiert ? Wann soll den bei meinem Vorschlag #WERT zurückkommen?
Wenn die aus dem String verbleibenden Zahlen mit NULL beginnen....bla bla ....
Mit meinem Vorschlag (ohne Multiplikation!) werden führende Nullen nicht zurückgegeben und es wird kein Fehlerwert zurückgegeben. Beim Vorschlag mit "0" im Format wird nur dann eine "0" zurückgegeben, wenn keine anderen Zahlen ausser einer oder mehreren Null(en) vorhanden sind .
Erst testen, dann motzen.
Greetz Renee

Anzeige
AW: Doch noch Missverständnis
30.08.2007 11:07:05
Renee
Hi again Peter,
Um alle Misverständnisse auszuräumen, mein Vorschlag musst Du in den Code einbauen!, er hat nix mit der Formatierung der Zellen zu tun!
Renee

AW: Doch noch Missverständnis
30.08.2007 11:18:11
Peter
Hallo Renée
Du hast recht. Ich wollte zwar alles umsetzen, habe jedoch nun entdeckt, dass ich die Codezeile
ZiffernAusString = Tx
nicht eliminiert habe.
Nochmals vielen Dank!
Gruss, Peter
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Alphanumerische Strings in Excel bereinigen

Schritt-für-Schritt-Anleitung

  1. Öffne Excel und erstelle eine neue Arbeitsmappe oder öffne deine bestehende Datei.

  2. Gehe zur Tabelle "Ursprung" und stelle sicher, dass deine Daten in Spalte A von Zeile 1 bis 200 stehen.

  3. Drücke ALT + F11, um den VBA-Editor zu öffnen.

  4. Füge ein neues Modul hinzu:

    • Klicke auf „Einfügen“ und wähle „Modul“.

  5. Kopiere den folgenden VBA-Code in das Modul:

    Function ZiffernAusString(Text As String) As String
   Dim Tx As String
   Dim i As Long
   For i = 1 To Len(Text)
       If Asc(Mid(Text, i, 1)) > 47 And Asc(Mid(Text, i, 1)) < 58 Then
           Tx = Tx & Mid(Text, i, 1)
       End If
   Next i
   If Tx = "" Then
       ZiffernAusString = ""
   Else
       ZiffernAusString = Format(Tx, "0")
   End If
End Function

  6. Schließe den VBA-Editor und gehe zurück zu Excel.

  7. Verwende die Funktion in der Zieltabelle:

    • In Zelle A1 der Tabelle "Ziel" schreibe: =ZiffernAusString(Ursprung!A1) und ziehe die Formel nach unten bis A200.

Häufige Fehler und Lösungen

  • #WERT! Fehler: Wenn der eingegebene String keine Zahlen enthält, stelle sicher, dass dein VBA-Code die Rückgabe für leere Strings abfängt. Das wird im obigen Code berücksichtigt.

  • Führende Nullen: Wenn führende Nullen bei der Ausgabe fehlen, kannst du die Formatierung im VBA-Code anpassen. Der Code entfernt diese durch die Multiplikation mit 1.

Alternative Methoden

  • Excel-Formel: Wenn du keine VBA-Lösung nutzen möchtest, kannst du auch eine Excel-Formel verwenden:

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

    Diese Formel funktioniert jedoch nur in neueren Excel-Versionen, die die Funktion TEXTJOIN unterstützen.

Praktische Beispiele

  • Beispiel 1:

    • Eingabe: AA104949B
    • Ausgabe: 104949

  • Beispiel 2:

    • Eingabe: A?10X939_
    • Ausgabe: 10939

  • Beispiel 3:

    • Eingabe: 13A17B19
    • Ausgabe: 131719

Tipps für Profis

  • Nutze die VBA-Funktion ZiffernAusString nicht nur zum Entfernen von Zeichen, sondern auch für komplexere Datenbereinigungen. Du kannst die Logik erweitern, um auch andere unerwünschte Zeichen zu entfernen.

  • Wenn du mit VBA Zeichen aus String entfernen möchtest, kannst du die Funktion anpassen, um mehr als nur Ziffern zu extrahieren.

FAQ: Häufige Fragen

1. Wie kann ich die Funktion in einer anderen Tabelle verwenden?
Du kannst die Funktion einfach in einer anderen Tabelle aufrufen, indem du den Tabellennamen vor der Zelle angibst, z.B. =ZiffernAusString(Tabelle2!A1).

2. Was passiert, wenn der String nur Buchstaben enthält?
In diesem Fall gibt die Funktion "" zurück, was bedeutet, dass kein Wert vorhanden ist.

3. Wie kann ich sicherstellen, dass die Funktion auch mit Kommas funktioniert?
Die derzeitige Funktion ignoriert jegliche nicht-numerischen Zeichen. Du kannst den Code so anpassen, dass auch Kommas entfernt werden, indem du die If-Bedingung erweiterst.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
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