Wie kann man eine Zahl zufälliger Länge und Position aus Excel Zelle auslesen?
Ich habe das Problem, dass ich einen Datensatz habe der als Text formatiert ist und eine Kombination aus Text und Zahlen beinhaltet.
Am Ende steht meist eine Zahlenkombination.
Mit Glätten, Teil, Finden habe ich mich jetzt schon soweit vorgekämpft, dass ich die Zahl vorne stehen habe in einer Zelle - aber in Ausnahmefällen habe ich noch einen Suffix, der ebenfalls zufällig ist.
Heißt:
Mehrheit meiner Daten sieht jetzt aus:
5679
487
5794
678
897
All das ist OK und was ich brauche.
Die Ausreißer sehen aber aus:
5679_UBD
5679ugd
678hgtsl
678_ikdwowdff
Heißt also sowohl die Zahl an der ich interessiert bin kann 3 oder 4 stellen haben (sonst keine Variation) als auch der Suffix kann durch "_" getrennt sein oder eben nicht - und die Stellen dahinter sind unterschiedlich lang.
Hat jemand eine Idee wie man das hinbekommt?
Ich wollte Excel dazu bringen zu erkennen was hier eine Zahl ist und mir nur diese ausgeben. Aber ich finde keine Lösung.
Alternativ wäre eine Variante von "WENN" hilfreich die überprüft ob bspw "678" in der Zelle enthalten ist und mir WAHR ausgibt egal ob NUR 678 enthalten ist oder ein zufälliger Suffix dran hängt.
Vielen Dank
5 Antworten
Ich hab mal diese Formel gebastelt:
=LET(
Zeichen; TEIL(A1; SEQUENZ(1; LÄNGE(A1)); 1);
Maske; NICHT(ISTFEHLER(WERT(Zeichen)));
Ziffern; FILTER(Zeichen; Maske);
WERT(TEXTKETTE(Ziffern))
)
Die beiden A1 musst du halt austauschen mit der Zelle, wo der Wert drinsteht, von dem du nur die Ziffern haben willst.
Achtung: Die Formeln behält alle Ziffern, die da drin stehen. Also wenn du einen Wert, wie z.B. 5678_AB3CD4 hast, kommt 567834 raus.
Zur Erklärung: LET ist eine nette Funktion, mit der du Zwischenergebnisse in Variablen speichern kannst.
Als erstes lege ich eine Variable namens "Zeichen" an, wo einfach eine Liste aller Zeichen drinsteht (also aus "5679_UBD" wird {"5"; "6", "7"; "9"; "_"; "U"; "B"; "D"})
Dann lege ich eine Maske an, wo einfach drinsteht, welche Zeichen in das Ergebnis übernommen werden sollen (also bei dem Beispiel kommt {WAHR; WAHR; WAHR; WAHR; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH} raus)
In die Variable "Ziffern" kommen dann nur die Ziffern rein (also {"5"; "6", "7"; "9"}) und die werden im letzten Schritt in die Zeichenfolge "5679" zusammengefasst und in die Zahl 5679 umgerechnet.
Eigentlich sollte es mit dem Ergebnis aus der Formel funktionieren. Die Formel gibt eine Zahl zurück. Du kannst aber natürlich auch im letzten Schritt die WERT Funktion weglassen, damit daraus ein Text wird. Vielleicht stehen die Zahlen in deiner Suchtabelle ja als Text drin, dann könnte die Suche funktionieren, wenn die gesuchten Werte auch Text sind.
Die Formatierung ist unabhängig von der Formel. Die kannst du ganz normal über Rechtsklick > Zellen formatieren... einstellen.
Ich hab leider keine Idee, woran es liegen kann, dass die Suche nicht geht. Da bräuchte ich die Excel-Datei, um dir helfen zu können.
Ja tatsächlich klappt es jetzt. Es lag an einem gesetzten Filter den ich übersehen habe.
Jetzt habe ich aber tatsächlich den Fall (dank dem dreckigen Datensatz den ich bekomme) den du beschreibst.
Bisher hatte ich immer solche Fälle:
5679_UBD
5679ugd
678hgtsl
678_ikdwowdff
Seit heute gibt es:
5679_UBD3
678htgsl3
Mich interessieren aber nur die vorderen Zahlen.
Ich wollte es einfach mit =Links(A1;4) lösen, da die Zahl max 4 Stellen hat.
Aber dann wird es zu Text formatiert - was mir die weitere Verwendung killt.
Natürlich kann ich copy & paste und als Zahl umwandeln.
Da das aber eine stetig wachsende Datenbank ist brauche ich immer automatisch die Ausgabe einer Zahl.
Noch eine Idee?
Wenn es immer drei oder vier Zahlen sind, bevor der Rest anfängt, kannst du einfach prüfen, ob das vierte Zeichen eine Zahl ist und dann entweder drei oder vier Ziffern ausschneiden:
=WENN(ISTFEHLER(WERT(TEIL(A1;4;1)));WERT(LINKS(A1;3));WERT(LINKS(A1;4)))
Wenn es da auch andere Längen gibt, muss ich nochmal ein bisschen rumexperimentieren. Da müsste man im Prinzip die erste Stelle suchen, die keine Zahl ist und dann nur die entsprechenden Zahlen kopieren, aber das wird etwas komplizierter.
Es könnten mehrere Schritte erforderlich sein. - Daten sortieren, damit wären die "nur Zahlen" von denn mit Text separiert.
Daten - Text in Spalten - feste Breite - zunächst die Trennung hinter der längsten Zahlen (... mit Text) den Trennmarker setzen.
Danach das gleiche mit den verbleibenden Zahlen mit Text wiederholen usw.
1. Schritt
'2.Schritt
Ergebnis
Mit der Lösung ergeben sich für mich 2 Probleme:
- Markiere ich die Felder über "Text in Spalten" wird mir die Formel dahinter angezeigt (es ist bereits das Ergebnis eines ersten Schritts) --> lösbar
- Der Datensatz wächst kontinuierlich und ist mehrere 1000 Zeilen groß. Ich verstehe die Lösung als nicht machbar für solche einen Einsatzzweck
Schau mal, ob Dir das weiter hilft. (... kann es nicht testen)
https://de.extendoffice.com/documents/excel/2701-excel-separate-text-and-numbers.html
Es würde auch mit Formeln gehen, die Variante von GutenTag2003 ist aber in 2 Schritten zu erledigen und damit ebenso effektiv.
Mit VBA müsste das gehen:
Function GetZahl(s As String) As Double
GetZahl = Val(s)
End Function
dann könnte in A1 stehen:
=GetZahl(B1)
Makros müssen aktivert sein.
Hallo,
dazu hilft nur ein Excel Addín, das alles bis auf die Ziffern löscht. Dieses Addín:
löscht alles außer Ziffern und die explizit ausgewählten Zeichen, die stehen bleiben sollen, wenn es solche Zeichen überhaupt gibt.
LG
Siegfried
Das funktioniert tatsächlich sehr gut und schlanker als über das Makro.
Nur scheint es am Ende doch keine Zahl zu ergeben (mit dem Makro ebenfalls nicht)
Ich lege einen XVERWEIS an und vergleiche das Resultat mit einer anderen Tabelle und bekomme #NA obwohl visuell die Zahlen in beiden Tabellen vorkommen.
Die Formatierung scheint wohl keine Zahl zu sein?