Excel: Text aus Zellen löschen, Zahlen behalten
Ich habe eine Liste von Bilddateien mit einer .bat-Datei in einer Excel-Tabelle auflisten lassen.
---> Dir /A /B /N /W /TC > ~DateiListe.xls <---
Die Namen der Dateien enthalten Artikelnummern. Die Artikelnummern stehen mal am Anfang, mal am Ende und mal mitten im Dateinamen, als Beispiel:
Schraube12345.jpg 14365Manchette.jpg Winkel34754gross.jpg
Ich möchte nur die Artikelnummern rausfiltern und alles an Text und Sonderzeichen löschen. Wie ich Excel kenne ist das möglich, die Frage ist wie^^
6 Antworten
Ok, hier mein (etwas unkonventioneller aber hoffentlich brauchbarer) Lösungsansatz:
Die Schwierigkeit in Deiner Aufgabe besteht darin, die erste und die letzte Position einer Ziffer innerhalb des Dateinamens zu finden. Um die Positionen zu ermitteln suche ich mittels FINDEN nach den Zahlen 0..9 am Anfang und am Ende.
Deine Tabelle sollte folgenden Aufbau haben:
- Zeile 1 bleibt leer.
- Die Dateinamen stehen in Spalte A ab Zeile 2 (z.B. A2:A100) Jetzt schreibst Du in Zeile 1 ab Spalte B folgende Werte:
- B1 bis K1: die Ziffern 0 bis 9
- L1: Min
- M1..V1: nochmal die Ziffern 0 bis 9
- W1: Max
- X1: Artikelnr. Es sollte jetzt wie in Bild 1 aussehen.
In Zelle B2 (unter "0") kommt nun folgende Formel:
=WENN(ISTFEHLER(FINDEN(TEXT(B$1; "0"); $A2)>=0);"";FINDEN(TEXT(B$1; "0"); $A2))
Diese Formel kopierst Du einfach in alle Zeilen der Spalten B bis K.
In Zelle L2 ("Min") folgende Formel:
=MIN(B2:K2)
Auch diese Formel in alle Zeilen der Spalte L kopieren.
In Zelle M2 ("0") kommt folgende Formel:
=WENN(ISTFEHLER(FINDEN(TEXT(M$1; "0"); $A2;$L2+1)>=0);"";FINDEN(TEXT(M$1; "0"); $A2;$L2+1))
Diese nun in alle Zellen der Spalten M bis V einfügen.
In W2 ("Max") steht (analog zu L2)
=MAX(M2:V2)
Ebenfalls kopieren.
Und zu guter letzt in Spalte X2 ("Artikelnr.")
=TEIL(A2;L2;W2-L2+1)
Wenn Du alles korrekt eingegeben hast, sollte nun in Spalte X die Artikelnummer stehen. (s. Bild 2) Achte vor allem auf die $-Zeichen der Formeln, sonst geht das Kopieren schief.
Zur Erläuterung: In den Spalten B bis K steht jeweils das erste Vorkommen der Ziffern 0..9 In Spalte L wird der kleinste Wert der Spalten B bis K ermittelt. In den Spalten M bis V steht jeweils das letzte Vorkommen der Ziffern 0..9 In Spalte W wird der größte Wert der Spalten M bis V ermittelt. Und in Spalte X wird einfach der Teilstring zurückgegeben.
Hoffe, es hilft Dir etwas weiter.
Vielen Dank für deine Mühe! Allerdings taucht noch ein kleiner Fehler auf: Manche Nummern werden abgeschnitten, es fehlen meist die letzten 1-2 Ziffern.
Ich weiß jetzt nicht wie man hier Bilder einfügt aber der unten stehende Link führt zu nem Screenshot. Hab mal alle Zeilen ausgeblendet wo es geklappt hat.
Hallo PhilEs,
prüfe mal bitte, ob in den Zellen unter Spalt M bis V auch die Formel aus meinem Kommentar steht und ob die in der Eingabezeile in geschweiften Klammern erscheint.
=MAX((TEIL($A2;SPALTE(1:1);1)=TEXT(M$1; "0"))*SPALTE(1:1))
Habe mal 2 Beispiele von Deinem Bild probiert und bei mir klappt es. Schau mal unter http://dl.dropbox.com/u/17078809/1.jpg nach. Wenns nicht klappt mach mal nen Screenshot, wo die Formel aus Zelle N2 zu sehen ist.
Ach, Mist, mir ist einfach nur ein Flüchtigkeitsfehler passiert.
Ich hatte deine ursprüngliche Formel für M bis V eingegeben und dann nach deinem Kommentar die Verbesserte Formel nachgetragen aber vergessen, die Formel bis "V" rüberzukopieren....
Jetzt klappt alles! Vielen Dank, das wird mir einiges an Arbeit abnehmen.
Ich gehe davon aus, dass nur EIN Zahlenblock vorkommt.
Angenommen Dein Dateiname steht in A1.
Dann zeigt Dir diese Formel den Zahlenteil an:
{=TEIL(A1;MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE(1:30);1)*1)*1)*ZEILE(1:30);0;30))*1);MAX((((ISTZAHL(TEIL(A1;ZEILE(1:30);1)*1)*1)*ZEILE(1:30))))-MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE(1:30);1)*1)*1)*ZEILE(1:30);0;30))*1)+1)}
[ACHTUNG!
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die {} und macht die Formel zu einer Matrixformel.]
Wahrscheinlich lässt sich das auch einfacher lösen, aber funktionieren sollte es so.
Mit Deinen drei Beispielen klappt es jeden Falls.
Teste mal ausgiebig.
Klappt es.
In der Formel müssen die 30 noch durch 999 ersetzt werden, sonst würde der Text nur bis zur 30 Stelle "untersucht" (hatte ich zu Testzwecken so eingestellt). Also wie oben, nur diese Formel:
{=TEIL(A1;MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE(1:999);1)*1)*1)*ZEILE(1:999);0;999))*1);MAX((((ISTZAHL(TEIL(A1;ZEILE(1:999);1)*1)*1)*ZEILE(1:999))))-MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE(1:999);1)*1)*1)*ZEILE(1:999);0;999))*1)+1)}
Wenn das mit dieser einen Formel hinhaut wäre das ja super. Die kann man sich zur Not auch mal wo wegkopieren.
Ich hab die Dateinamen ab A1 untereinander aufgelistet und in B1 die Formel eingetragen. Hab diese auch ohne die geschweiften Klammern kopiert und mit STRG + Shift + ENTER abgeschlossen, die geschweiften Klammern sind daraufhin auch erschienen.
Allerdings tauchen auch hier Fehler auf:
Autsch! Da habe ich nicht weit genug gedacht.
Da müssen absolute Bezüge rein, damit es beim Runterkopieren richtig bleibt.
Also meine
{=TEIL(A1;MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE($1:$999);1)1)1)ZEILE($1:$999);0;999))1);MAX((((ISTZAHL(TEIL(A1;ZEILE($1:$999);1)1)1)ZEILE($1:$999))))-MIN((WECHSELN((ISTZAHL(TEIL(A1;ZEILE($1:$999);1)1)1)ZEILE($1:$999);0;999))*1)+1)}
Sorry, war ein blöder Flüchtigkeitsfehler, ich bin wohl doch schon zu sehr aus der Übung.
SO müsste es jetzt aber hinhauen.
Da war ich wohl etwas voreilig mit Vergabe der hilfreichsten Antwort.
So funktioniert es super.
Hab heute aber noch einen Lieferanten entdeckt, der bei ner kleinen Anzahl an Artikeln Dateinamen wie "12345_AB-06.jpg" hat.Da übernimmt die Formel die alten Dateinamen einfach so wie sie sind. Ist aber nicht so wild, da es nur ein paar ganz wenige sind die ich in 10min per Hand ändern kann.
Super Hilfe, danke!
Hast Du die Möglichkeit, mehrere Spalten in Deiner Tabelle als "Hilfsspalten" zu nutzen? Die können dann bei Bedarf auch gern ausgeblendet werden. Dann hätte ich eine Lösung, die offensichtlich auch zuverlässig funktioniert - auch ganz ohne VBA.
Die Excel-Tabelle ist nur für mich. Was da letztendlich drinsteht in den Spalten interessiert nachher niemanden, kann da also soviel rumpfuschen wie ich will, hauptsache ich erreiche mein Ziel^^
Probier mal unter - Filtern- den "Textfilter"
Musste erst nen Filter in oberste Spalte einbauen, dann auf den Filter knopf rechts
und im Dropdown dann den Textfilter, da sind jede Menge Einstelllungen
Danke für deine schnelle Antwort!
Aber damit kann ich ja einfach nur alles anzeigen lassen, was eine bestimmte Vorgabe erfüllt.
Ich möchte ja aber die Bildnamen in der Excel-Datei direkt ändern um die dann weiterverarbeiten zu können.
Also grob gesehen will ich Excel sagen "Entferne aus den Zellen alles was keine Zahl ist!"
Aus "Schraube_12345.jpg" Soll "12345.jpg" werden.
Das Stimmt schon, aber wenn z.B. die Nummer dann in B1 steht ohne ".jpg" löse ich das mit =B1&".jpg"
Das ist nicht das Problem bei der Sache :-)
Hallo PhilEs,
wenn die Artikelnummer immer mit der gleichen Ziffer beginnen würde und die Artikelnummer immer gleich lang wäre, könnte ich mir eine Lösung ohne VBA vorstellen. Kannst du das für diese Aufgabe zusichern?
Gruß Distel
Hallo Distel35,
leider nicht, die jeweils erste Ziffer variiert von 0-9 und auch die Länge ist verschieden. Allerdings habe ich herausgefunden, dass die ersten Zahlen die auftauchen immer die Artikelnummern sind und mit einem Unterstrich vom Rest des Namens getrennt sind. Ich weiß nicht ob es eine elegantere Lösung gibt, aber ich habe es jetzt mit
=ERSETZEN(A1;FINDEN("_";A1;1);100;"")&".jpg"
versucht und in diesem Fall scheint es ne gute Lösung zu sein.
Das war jetzt aber ein Lieferant von ca 800. und da wird es bei einigen garantiert noch der Fall sein, dass die Artikelnummern nicht so eindeutig zu unterscheiden sind.
Allerdings bin ich in Sachen VBA so gar nicht zu Hause. Wenn es also so eine Lösung wird, bräuchte ich ne Anleitung für absolute Anfänger^^
Hallo PhilEs,
deine Formel liefert aber nur dann das gewünschte Ergebnis, wenn der Dateiname mit der Artikelnummer beginnt und direkt hinter der Artikelnummer ein Tiefstrich steht.
Also etwa so:
14365 _ Manchette.jpg ---> 14365.jpg
nicht funktionert die Formel, wenn der Dateiname mit Buchstaben beginnt,
etwa so:
Winkel _ 134754gross.jpg --> Winkel.jpg
da deine Formel alles was ab dem Tiefstrich kommt abschneidet und dann noch ein .jpg anhängt.
Hilfreich wäre es, wenn du echte Dateinamen als Beispiel hier reinstellen würdest.
Gruß Distel
Ich sag ja bei DIESEM Lieferanten ging es so, da alle nach [ARTIKELNUMMER]_XYZ.jpg aufgebaut waren.
Also ich hab jetzt noch ungefähr 800 Lieferanten vor mir mit jeweils mehreren hundert Artikeln. Da kommen sämtliche Variationen der Dateinamen vor. Nummer am Anfang, Nummer am Ende, Nummer mittendrin,vor/nach Sonderzeichen/Zahlen, Nummer mit 4 Stellen, Nummer mit 10 Stellen....^^
Das Einzige was ich sicher sagen kann: Alle beinhalten irgendwo die Artikelnummer. Ich schätze da darf ich mir dann für jeden Lieferanten was individuelles zusammenbasteln^^
Hier noch ein kleiner Verbesserungsvorschlag: Statt wie o.g. gib in Spalte M2 lieber folgende Formel ein:
Achtung: Da es sich hier um eine Matrix-Formel handelt unbedingt die Eingabe mit Shift+Strg+Enter abschließen (zu erkennen, daß in der Eingabezeile die Formel dann in geschweiften Klammern steht)!!
Diese Formel sucht wirklich das letzte Vorkommen eines Zeichens. Die Formel aus der obigen Antwort kann zu Fehlern führen, wenn eine Ziffer mindestens 3-mal in in der Artikelnummer vorkommt und gleichzeitig an letzter Stelle steht. (Aus Schraube045020.jpg käme dann 04502 statt 045020 heraus). Mit der Matrixformel ist auch dieses Problem behoben.