Excel: Text aus Zellen löschen, Zahlen behalten

6 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

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.

Bild 1 - (Excel, Formel, Filter) Bild 2 - (Excel, Formel, Filter)

Hier noch ein kleiner Verbesserungsvorschlag: Statt wie o.g. gib in Spalte M2 lieber folgende Formel ein:

    =MAX((TEIL($A2;SPALTE(1:1);1)=TEXT(M$1; "0"))*SPALTE(1:1))

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.

0
@puba07

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.

http://666kb.com/i/btt4yp8dx8f2mm3xq.gif

0
@PhilEs

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.

0
@puba07

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.

0
@PhilEs

Freut mich wenn ich Dir helfen konnte. War auch eine interessante Aufgabe für mich - mal richtig quer zu denken ;-) Vielen Dank!

0

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^^

0

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.

Korrektur:

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)}

0

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:

http://666kb.com/i/btt585zmlouojsxgu.gif

0
@PhilEs

Autsch! Da habe ich nicht weit genug gedacht.
Da müssen absolute Bezüge rein, damit es beim Runterkopieren richtig bleibt. Also meine

Korrektur2:

{=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.

0
@Oubyi

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!

0

Kann ich in Excel per Formel einen Teil eines Feldinhalts löschen?

Hi all,

ich exportiere aus einem Programm heraus Daten in eine Excel Tabelle. Diese Einträge bestehen aus einem Datum, etwas Text und einer Nummer, die als "Zeichenkette" in ein Feld geschrieben werden.. Besteht die Möglichkeit, den Feldinhalt mit Ausnahme der Nummer zu löschen bzw. sich die Nummer aus dem Feld zu ziehen? Denn die brauche ich zur weiteren Verarbeitung, möchte die aber bei ca. 300 Feldern nicht jeweils einzeln rauskopieren. Vielleicht geht das ja über eine Formel, nur reichen meine Excel Kenntnisse nicht aus.

Viele Grüße mydigitalme

...zur Frage

Textzellen in Excel nur den Text in Anführungszeichen anzeigen lassen?

Ich habe verschiedene Zellen untereinander mit teilweise Wörter oder sätze eingeschlossen in Anführungszeichen. Das ist in Spalte A:A. In Spalte B:B soll nur der Text in "" ausgegeben werden.

...zur Frage

Zahl und Text in einer Zelle, ich kann damit aber nicht rechnen weil excel die Zellen als Wort sieht

Ich habe in den Zellen 11 Fahrten , 10 Fahrten, 7 Fahrten usw. was muss ich machen damit excel die zellen auch als zahl sieht damit ich mit den zellen rechnen kann und die summe aller fahrten weiß.

wenn ich jetzt alles einfach so summiere dann kommt " #WERT! " raus.

danke

...zur Frage

Zellen verknüpfen im Microsoft Excel

Hallo, wie verknüpft man im Excel mehrere Zellen so miteinander, dass falls in einer irgendwelche Änderungen auftreten (z.B der Text wird geändert), das gleich auch in der Verknüpften Zelle geschieht?

Gibt es überhaupt so eine Funktion?

Ich bedanke mich :)

...zur Frage

EXCEL: Bestimmtes Zeichen hinter Text in einer Spalte löschen?

Meine Spalte A sieht wie folgt aus:

20500 X * 60500 X * 37452 X 5 22371 X *

etc.

Ich möchte jetzt bei allen Artikeln mit Stern den Stern löschen. Gibt's da ne Möglichkeit ohne in jede einzelne Zelle gehen zu müssen?

...zur Frage

Was möchtest Du wissen?