Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1704to1708
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Erste Zahl in alphanumerischer Zelle extrahieren

Erste Zahl in alphanumerischer Zelle extrahieren
02.08.2019 09:59:50
Kah
Hallo zusammen,
ich habe folgendes Problem:
Ich habe in einer Spalte einer Excel Tabelle sehr viele alphanumerische Identifikationsnummer vorliegen, die alle unterschiedlich lang sind.
Jedoch interessiert mich nur die erste Zahl dieser Kennnummer und diese möchte ich gerne in der Spalte daneben ausgeben lassen.
Beispielsweise können die Kennnummer wie folgt aussehen:
ab1248524 - es interessiert mich nur die 1
auifbf77 - es interessiert mich nur die 7
b5b4b2 - es interessiert mich nur die 5

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
erste Zahl extrahieren
02.08.2019 10:13:34
WF
Hi,
folgende Arrayformel:
{=TEIL(A1;VERGLEICH(WAHR;ISTZAHL(TEIL(A1;ZEILE(1:19);1)*1);0);1)]
WF
AW: Erste Zahl in alphanumerischer Zelle extrahieren
02.08.2019 10:22:01
fcs
Hallo Kah Seng,
mit der folgenden Formel, die du als Matrix-Formel eingeben muss (Eingabe der Formel mit Strg+Enter abschliessen), sollte es funktionieren.
=WENNFEHLER(TEIL(A1;MIN(WENN(ISTFEHLER(SUCHEN({0.1.2.3.4.5.6.7.8.9};A1));99; SUCHEN({0.1.2.3.4.5.6.7.8.9};A1)));1);"") 

Zelle A1 musst du entsprechend deinen Zellen anpassen.
LG
Franz
AW: Erste Zahl in alphanumerischer Zelle extrahieren
02.08.2019 12:02:34
Kah
Vielen Dank für die Antworten!
Leider hat die Antwort von WF bei mir nicht funktioniert bei mir. Vielleicht habe ich es auch falsch eingegeben, aber welchen Sinn hat der Teil (Zeile 1:19) bei der Formel?
@Franz: deine Formel hat zwar genau das geliefert was ich wollte, aber die Berechnung dauert unglaublich lange. Gibt es eine Möglichkeit das irgendwie zu beschleunigen?
Anzeige
ZEILE(1:19) ist nur der untersuchte Bereich
02.08.2019 12:14:45
WF
ich ging davon aus, dass die erste Zahl innerhalb der ersten 19 Zeichen vorkommt.
Kannst Du gnadenlos ausdehnen: - z.B.:
{=TEIL(A1;VERGLEICH(WAHR;ISTZAHL(TEIL(A1;ZEILE(1:99);1)*1);0);1)}
WF
AW: ZEILE(1:19) ist nur der untersuchte Bereich
02.08.2019 13:32:11
Kah
Ah, okay! Mittlerweile klappt es auch super mit der Formel, danke WF!
Leider hab ich jedoch ein weiteres Problem, da ich mit der ausgegebenen Zahl nicht weiterarbeiten kann. Ich versuch euch mal das ganze Problem zu erklären mit einem ausgedachten Beispiel (darf die Firmendatei nicht hochladen):
Ich habe zu einem mehrere Gerätetypen, die mit jeweils einheitlichen Namen gekennzeichnet sind in einer Spalte, dazu habe ich unglaublich viele Identifikationsnummer und in einer anderen Spalte die Anzahl der Geräte.
Bohrer abc123 5
Hammer ac454 7
Bohrer adcf4 1
Schlüssel bf1 4
Ziel ist es diese Geräte zwei Generationen zuzuordnen. Es gibt Generation 1 und 4, die jeweils erkenntlich sind an der ersten Zahl der Identifikationsnummer. Generation 1 und 4 existieren aber nur für Bohrer und Hammer.
Meine bisherige Idee war es die Zahl mit eurer Formel in der Spalte G ausgeben zu lassen und dann zu prüfen ob die Gerätekategorie passt und die Zahl in der Spalte G passt, um dann die Anzahl der Geräte ausgeben zu lassen.
Für die Generation 1 sähe es dann so aus (in G1 steht die ausgelesene Zahl aus der Kennnummer und in F2 die Anzahl der Geräte):
=WENN(UND(ODER(A1="Bohrer";A1="Hammer");G1="1");WERT(F2);0)
Leider erkennt die Formel aber die ausgelesene Zahl nicht als solche und gibt mir immer den sonstigen Wert 0 aus.
Ich hoffe, dass das nicht zu umständlich erklärt war. Hab versucht es einfach zu halten.
Anzeige
das Ergebnis von TEIL ist immer Text
02.08.2019 13:42:24
TEIL
multipliziere die Formel mit 1 - und das war's.
{=TEIL(A1;VERGLEICH(WAHR;ISTZAHL(TEIL(A1;ZEILE(1:99);1)*1);0);1)*1}
WF
AW: Erste Zahl in alphanumerischer Zelle extrahieren
02.08.2019 12:49:31
fcs
Hallo Kah Seng,
wenn du sehr viele Datenzeilenhast, dann dauetr die Neuberechnung entsprechend lang - ist bei Matrixformeln halt meist so.
Probiere die Formel von WH.
Evtl. ist die schneller.
Bei der Eingabe musst du die geschweiften Klammern am Anfang und Ende weglasen!
Diese werden von Excel erzeugt wenn man die Eingabe mit Strg+ENTER abschliesst, um eine Matrixformel zu erzeugen.
LG
Franz
AW: Erste Zahl in alphanumerischer Zelle extrahieren
02.08.2019 13:06:06
Daniel
Hi
wenn du Excel 2010 oder höher hast, probier mal das:
=TEIL(A1;AGGREGAT(15;6;ZEILE($Z$1:$Z$99)/ISTZAHL(-TEIL(A1;ZEILE($Z$1:$Z$99);1));1);1)

um die Rechenzeit so gering wie möglich zu halten, solltest du den Zellbezug in der Funktion ZEILE() so genau wie möglich an deine Anforderungen anpassen.
dh wenn du beispielsweise weißt, dass die erste Ziffer spätestens nach 20 Zeichen im Text erscheinen muss, kannst du hier die 20 eintragen.
=TEIL(A1;AGGREGAT(15;6;ZEILE($Z$1:$Z$20)/ISTZAHL(-TEIL(A1;ZEILE($Z$1:$Z$20);1));1);1)

ein größerer Zellbereich schadet im Ergebnis nicht, braucht aber mehr Rechenzeit.
ein zu kleiner Zellbereich führt zu einem Fehler, wenn die erste Ziffer später auftaucht.
Gruß Daniel
Anzeige
AW: Erste Zahl in alphanumerischer Zelle extrahieren
02.08.2019 13:13:59
Daniel
hier nochmal die Idee von Franz etwas anders und damit vielleichte schneller umgesetzt, auch hier ist wieder mindestens Excel 2010 erforderlich:
=TEIL(A1;AGGREGAT(15;6;FINDEN({1;2;3;4;5;6;7;8;9;0};A1);1);1)
welche der beiden Varianten jetzt schneller ist, musst du prüfen.
Gruß Daniel
kann man kürzen
02.08.2019 13:37:01
WF
=TEIL(A1;AGGREGAT(15;6;FINDEN(ZEILE(1:10)-1;A1);1);1)
AW: kann man kürzen, ist aber hier nicht sinnvoll
02.08.2019 13:45:48
Daniel
denn:
1. wird die Formel jetzt jedesmal neu berechnet, wenn sich in irgendeiner Zelle in den Zellen 1:10 ein Zellwert ändert, dh die Formeln werden "teilvolatil" und in Fällen neu berechnet, in denen das nicht notwendig wäre, das ist nicht gut für die Performance der Datei.
2. muss die Formel in allen Zellen angepasst und korrigiert werden, wenn eine der Zeilen 1-10 gelöscht wird, weil dann aus dem "Zeile(1:10)" ein "Zeile(1:9)" wird und somit nach einem Löschen von Zeilen nicht mehr alle Ziffern überprüft werden.
du siehst, Kürzen um jeden Preis ist nicht immer sinnvoll.
Gruß Daniel
Anzeige
ich komme aus der Praxis
02.08.2019 14:02:43
WF
wann hab ich in einer fertigen Tabelle mal ne Zeile gelöscht ?
AW: ich komme auch aus der Praxis
02.08.2019 14:20:44
Daniel
und da brauchst du mir nichts zu erzählen.
es mag ja sein, dass du ein "Mister Perfect" bist, der alle seine Tabellen zuerst auf dem Papier vollständig und fehlerfrei vorplant, so dass du NIEMALS eine nachträgliche Änderung in deinen Tabellen notwendig hast, aber das ist meiner Praxiserfahrung nach eher die Ausnahme.
auch wenn, bleiben die anderen Punkte:
der Fragestelle berichtet von hohen Rechenzeiten, warum also die Formel unnötigerweise neu berechnen lassen?
und was ist der Vorteil deiner Variante, mal abgesehen davon, dass sie ein paar wenige Zeichen kürzer ist?
Ich lerne ja gerne dazu, daher würde ich dich um eine aussagekräfitige Antwort bitten.
Gruß Daniel
Anzeige
ich komme auch aus der Praxis ...
02.08.2019 14:22:37
lupo1
... und mein Zahn tut immer noch weh.
Gute Besserung
02.08.2019 14:50:19
Daniel
Gruß Daniel
statt ZEILE(A1:A999)
02.08.2019 20:13:16
WF
nimmst Du konsequenterweise also
{1;2;3;4;5;6 ......... 999}
ich nicht
WF
aus Sturheit würdest du das nehmen
02.08.2019 20:17:40
Daniel
ich arbeite mit Maß und Ziel und suche für die jeweilige Situation die am besten passende Lösung.
ein Prinzip, dass dir nicht zu liegen scheint, da du anscheinend nur nach vorgegebnen Schema F arbeiten kannst.
Gruß Daniel
AW: Erste Zahl in alphanumerischer Zelle extrahieren
02.08.2019 13:42:32
Günther
Moin,
... oder mit meinem Lieblings-Tool (meist sehr schnell): => http://www.excel-ist-sexy.de/pq-zahl-aus-text-extrahieren-separieren/ Da müsste die Formel in der Benutzerdefinierten Spalte nur noch mit

Text.Start([Spalte1],1)
eingefasst werden.
Gruß
Günther
Anzeige
Du könntest es z. B. auf diese...
02.08.2019 15:22:50
Case
Hallo, :-)
... Art probieren: ;-)
RegExp...
Servus
Case

305 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige